今天在项目中需要清理某个表的垃圾数据，通过delete from table where field in(子查询)失败，特来研究下删除下in和not in的问题

(1).普通in/not in正确

DELETE FROM member_extend WHERE uid IN ( 4, 5 ) 
DELETE FROM member_extend WHERE uid NOT IN ( 4, 5 )

(2).子查询in/not中没有包含where所属的表名,正确

DELETE  FROM  member_extend  WHERE  uid  IN(
    SELECT id  FROM member
)
DELETE  FROM  member_extend  WHERE  uid NOT IN(
    SELECT id  FROM member
)

(3).子查询in中包含where所属的表名,错误:You can't specify target table 'member_extend' for update in FROM clause

DELETE  FROM  member_extend  WHERE   uid  IN(
  SELECT uid FROM member_extend
)
DELETE  FROM  member_extend  WHERE   uid  NOT IN(
  SELECT uid FROM member_extend
)
DELETE  FROM  member_extend  WHERE   uid  NOT IN(
  SELECT b.uid  FROM member  a LEFT JOIN member_extend b on a.id=b.uid
)

通过上面的(3)实例我们可以看出来,在delete where 子查询中不能直接包含where所属的表名,例如我们要删除的是member_extend表的数据,子查询中也直接出现member_extend表的数据,我们只需要再包装一层,并加上别名即可。

上面(3)实例中的正确代码修正后的方式:

DELETE  FROM  member_extend  WHERE   uid  IN(
     SELECT uid FROM (SELECT uid FROM member_extend) a
)
DELETE  FROM  member_extend  WHERE   uid  NOT IN(
     SELECT uid FROM (SELECT uid FROM member_extend) a
)
DELETE  FROM  member_extend  WHERE   uid  NOT IN(
    SELECT uid FROM (SELECT b.uid  FROM member  a LEFT JOIN member_extend b on a.id=b.uid) AS b
)