今天看到一个，然后尝试一下
表连接后做update
mysql> select * from b;
+---------------------+------+------+
| Time | id | test |
+---------------------+------+------+
| 2007-08-11 21:15:07 | 1 | NULL |
| 2007-08-11 21:15:09 | 2 | NULL |
| 2007-08-11 21:15:11 | 3 | NULL |
| 2007-08-13 19:49:32 | 4 | NULL |
| 2007-08-13 19:55:32 | NULL | NULL |
+---------------------+------+------+
5 rows in set (0.00 sec)
mysql> select * from a;
+---+------+------+
| a | c | id |
+---+------+------+
| 1 | 2 | 3 |
| 3 | 4 | 4 |
+---+------+------+
2 rows in set (0.00 sec)
mysql> update b inner join ( select * from a ) using (id) set test = 1;
ERROR 1248 (42000): Every derived table must have its own alias
mysql> update b inner join ( select * from a ) as chosen using (id) set test = 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from b;
+---------------------+------+------+
| Time | id | test |
+---------------------+------+------+
| 2007-08-11 21:15:07 | 1 | NULL |
| 2007-08-11 21:15:09 | 2 | NULL |
| 2007-11-15 11:49:39 | 3 | 1 |
| 2007-11-15 11:49:39 | 4 | 1 |
| 2007-08-13 19:55:32 | NULL | NULL |
+---------------------+------+------+
5 rows in set (0.00 sec)
直接像postgresql好像也可以，记得oracle不行的
mysql> select * from b;
+---------------------+------+------+
| Time | id | test |
+---------------------+------+------+
| 2007-08-11 21:15:07 | 1 | NULL |
| 2007-08-11 21:15:09 | 2 | NULL |
| 2007-11-15 11:49:39 | 3 | 1 |
| 2007-11-15 11:49:39 | 4 | 1 |
| 2007-08-13 19:55:32 | NULL | NULL |
+---------------------+------+------+
5 rows in set (0.00 sec)
mysql> update b,a set test = 0 where b.id = a.c;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from b;
+---------------------+------+------+
| Time | id | test |
+---------------------+------+------+
| 2007-08-11 21:15:07 | 1 | NULL |
| 2007-11-15 11:54:19 | 2 | 0 |
| 2007-11-15 11:49:39 | 3 | 1 |
| 2007-11-15 11:54:19 | 4 | 0 |
| 2007-08-13 19:55:32 | NULL | NULL |
+---------------------+------+------+
5 rows in set (0.00 sec)