[Err] 1093 - You can't specify target table 's' for update in FROM clause

[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;