Hi all, I'm facing a strange behaviour with an UPDATE statement.
I have a table like: +---------+------+ | item_id | rank | +---------+------+ | 2812 | 2 | | 13050 | 4 | | 13051 | 3 | | 13052 | 1 | +---------+------+ And I want to switch items 2812 and 13052 rank (i.e. assign rank 2 to 13052 and assign rank 1 to 2812). I tried 2 update queries and only one works as expected even if the 2 queries looks very close: * First query: UPDATE item i1, item i2 SET i2.rank = i1.rank, i1.rank = 1 WHERE i1.item_id = 2812 AND i2.item_id = 13052; Query OK, 1 row affected (0.00 sec) Rows matched: 2 Changed: 1 Warnings: 0 +---------+------+ | item_id | rank | +---------+------+ | 2812 | 1 | | 13050 | 4 | | 13051 | 3 | | 13052 | 1 | +---------+------+ -> Doesn't work: change only one row. * Second query UPDATE item i1, item i2 SET i1.rank = i2.rank, i2.rank = 2 WHERE i1.item_id = 13052 AND i2.item_id = 2812; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 +---------+------+ | item_id | rank | +---------+------+ | 2812 | 2 | | 13050 | 4 | | 13051 | 3 | | 13052 | 1 | +---------+------+ -> It works. Can someone explain to me what happen ? Cheers, Manuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
