[Err] 1093 - You can't specify target table 's' for update in FROM clause
执行SQL
DELETE from book WHERE id IN(
SELECT id FROM (
SELECT id,name FROM book WHERE name IN(
SELECT name FROM book GROUP BY name HAVING count(name) > 1
)
) t WHERE id NOT IN (
SELECT id FROM book GROUP BY name HAVING count(name) > 1
)
);
出现错误:
[Err] 1093 - You can't specify target table 's' for update in FROM clause
执行SQL语句时出现这个错误。原因是在更新这个表和数据时又查询了它,而查询的数据又做了更新的条件
解决方法:把要删除的数据查询出来做为一个第三方表,然后进行删除。
DELETE book from book ,
(
SELECT id FROM (
SELECT id FROM book WHERE name IN(SELECT name FROM book GROUP BY name HAVING count(name) > 1)
) t
WHERE id NOT IN (SELECT id FROM book GROUP BY name HAVING count(name) > 1)
) as a
WHERE book.id = a.id;