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