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