在运行MySQL程序的时候会遇到You can’t specify target table ‘Grade1’ for update in FROM clause的错误,更新的时候子查询不能直接select更新的表。
例如:
创建一个辅助表来说明
CREATE TABLE Grade1(num INT,id INT,lastname VARCHAR(20),kid INT,kname VARCHAR(20),fenshu INT);
INSERT INTO Grade1 VALUES
(1,2005001 ,‘张三’, 1, ‘语文’, 81),
(2,2005001 ,‘李四’, 1, ‘语文’, 81),
(3,2005001 ,‘张三’, 1, ‘语文’, 81),
(4,2005001 ,‘张三’, 1, ‘语文’, 81);
DELETE FROM Grade1
WHERE num NOT IN(
SELECT MIN(num)
FROM Grade1
GROUP BY id,lastname,kid,kname,fenshu);
更改后:
DELETE FROM Grade1
WHERE num NOT IN(
SELECT a.b FROM
(SELECT MIN(num) b
FROM Grade1
GROUP BY id,lastname,kid,kname,fenshu)AS a);
此问题值存在于MySQL数据库。