2007/2/15, Manuel Vacelet <[EMAIL PROTECTED]>:
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
Nobody can explain what happens ?
-- Manuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
