匿名用户
1级
2011-02-12 回答
mysql> select * from pla;
+------+------+-------+
| id | vc | acc |
+------+------+-------+
| 1 | 100 | admin |
+------+------+-------+
1 row in set (0.00 sec)
mysql> UPDATE
-> pla
-> SET
-> vc=vc+2000
-> WHERE
-> acc='admin'
-> AND id=(SELECT MAX(id)
-> FROM pla WHERE acc='admin');
ERROR 1093 (HY000): You can't specify target table 'pla' for update in FROM clause
直接更新报错。
mysql> CREATE VIEW tmp as SELECT acc, MAX(id) AS max_id FROM pla GROUP BY acc;
Query OK, 0 rows affected (0.03 sec)
创建视图
修改更新语句。
mysql> UPDATE
-> pla
-> SET
-> vc=vc+2000
-> WHERE
-> acc='admin'
-> AND id IN (SELECT max_id
-> FROM tmp WHERE acc='admin');
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
检查更新结果:
mysql> select * from pla;
+------+------+-------+
| id | vc | acc |
+------+------+-------+
| 1 | 2100 | admin |
+------+------+-------+
1 row in set (0.00 sec)