今天在项目中需要清理某个表的垃圾数据，通过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 )
