Description:
UPDATE on two tables doesn't work correctly when inner joining by a comma.
As the manual said,
--------------------------------------------------------------
Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables:
UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
The example shows an inner join using the comma operator, but multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.
--------------------------------------------------------------
However, multiple tables update operation seems to behaviour questionably.
I just wanna swap the value of name column in two tables. I find "update tab1, tab2" operates differently with "update tab2, tab1".
update tab1, tab2 set tab1.name=tab2.name, tab2.name=tab1.name where tab1.id=tab2.id
update tab2, tab1 set tab1.name=tab2.name,
tab2.name=tab1.name where tab1.id=tab2.id
How to repeat:
mysql> create table tab1(id int, name int);
Query OK, 0 rows affected (0.09 sec)
mysql> create table tab2(id int, name int);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into tab1 values(1, 9), (2, 13);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into tab2 values(1, 27), (2, 5);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> update tab1, tab2 set tab1.name=tab2.name, tab2.name=tab1.name where tab1
.id=tab2.id;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 4 Changed: 2 Warnings: 0
mysql> select * from tab1;
+------+------+
| id | name |
+------+------+
| 1 | 27 |
| 2 | 5 |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from tab2;
+------+------+
| id | name |
+------+------+
| 1 | 27 |
| 2 | 5 |
+------+------+
2 rows in set (0.00 sec)
mysql> delete from tab1;
Query OK, 2 rows affected (0.00 sec)
mysql> insert into tab1 values(1, 9), (2, 13);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> update tab2, tab1 set tab1.name=tab2.name, tab2.name=tab1.name where tab1
.id=tab2.id;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 4 Changed: 2 Warnings: 0
mysql> select * from tab1;
+------+------+
| id | name |
+------+------+
| 1 | 9 |
| 2 | 13 |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from tab2;
+------+------+
| id | name |
+------+------+
| 1 | 9 |
| 2 | 13 |
+------+------+
2 rows in set (0.00 sec)
