mysql修改指定记录_mysql修改5条记录中某条记录的问题

匿名用户

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)