Re: [sqlalchemy] MySQL significant order in set list of update command
2011/12/11 Michael Bayer mike...@zzzcomputing.com My impression was Postgresql handles this much more nicely which is confirmed by this post: http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ the usual way this kind of thing is done is via declaring local variables in the SQL statement, and an example of doing such using MySQL's syntax is in that linked article - MySQL will let you assign to the variable in the WHERE clause so they do update t set a=b, b=@temp where (@temp:=a) is not null. This basically gets at the value of a before it changes, which is what PG does in any case. None of this has much to do with SQLAlchemy, though. you'd pretty much need to use a plain string or @compiles elements to produce that SQL within SQLAlchemy. I know about correct behavior of postgresql in this task. And I was very surprising when found such weird behavior of mysql. Is this a bug in mysql? What tell sql standard about such operations? PS For now I made switch of column values in client code, by doing select for update ... and update each row of result. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] MySQL significant order in set list of update command
Hi, All. I have found unexpected behaviour of MySQL DB. In update command order of operations in set list is significant. I need to switch values of 2 rows. Better see example: mysql create temporary table sw_test (a integer not null, b integer not null, dummy integer); Query OK, 0 rows affected (0.23 sec) mysql insert into sw_test (a, b) values (1, 2), (4, 3); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql update sw_test set a=b, b=a where ab; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from sw_test; +---+---+---+ | a | b | dummy | +---+---+---+ | 2 | 2 | NULL | | 4 | 3 | NULL | +---+---+---+ 2 rows in set (0.00 sec) Here update lost values of one row... but if we write: mysql update sw_test set dummy=a, a=b, b=dummy, dummy=null where ba; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from sw_test; +---+---+---+ | a | b | dummy | +---+---+---+ | 2 | 2 | NULL | | 3 | 4 | NULL | +---+---+---+ 2 rows in set (0.00 sec) Very ugly but what else? Is there a way to forse order of columns in SA update clause? PS: mysql-5.1.56 sqlalchemt-0.6.7 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] MySQL significant order in set list of update command
My impression was Postgresql handles this much more nicely which is confirmed by this post: http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ the usual way this kind of thing is done is via declaring local variables in the SQL statement, and an example of doing such using MySQL's syntax is in that linked article - MySQL will let you assign to the variable in the WHERE clause so they do update t set a=b, b=@temp where (@temp:=a) is not null. This basically gets at the value of a before it changes, which is what PG does in any case. None of this has much to do with SQLAlchemy, though. you'd pretty much need to use a plain string or @compiles elements to produce that SQL within SQLAlchemy. On Dec 10, 2011, at 8:21 AM, bogun.dmit...@gmail.com wrote: Hi, All. I have found unexpected behaviour of MySQL DB. In update command order of operations in set list is significant. I need to switch values of 2 rows. Better see example: mysql create temporary table sw_test (a integer not null, b integer not null, dummy integer); Query OK, 0 rows affected (0.23 sec) mysql insert into sw_test (a, b) values (1, 2), (4, 3); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql update sw_test set a=b, b=a where ab; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from sw_test; +---+---+---+ | a | b | dummy | +---+---+---+ | 2 | 2 | NULL | | 4 | 3 | NULL | +---+---+---+ 2 rows in set (0.00 sec) Here update lost values of one row... but if we write: mysql update sw_test set dummy=a, a=b, b=dummy, dummy=null where ba; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from sw_test; +---+---+---+ | a | b | dummy | +---+---+---+ | 2 | 2 | NULL | | 3 | 4 | NULL | +---+---+---+ 2 rows in set (0.00 sec) Very ugly but what else? Is there a way to forse order of columns in SA update clause? PS: mysql-5.1.56 sqlalchemt-0.6.7 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
