假设有一个用户表 user,数据如下:
SELECT id, uid, name FROM USER WHERE uid IN (SELECT uid FROM USER GROUP BY uid HAVING COUNT(uid) > 1);
SELECT id, uid, name FROM user WHERE uid IN (SELECT uid FROM user GROUP BY uid HAVING COUNT(uid) > 1) AND id NOT IN (SELECT MIN(id) FROM user GROUP BY uid HAVING COUNT(uid) > 1);
DELETE FROM USER WHERE id IN (SELECT u.id FROM (SELECT id FROM USER WHERE uid IN (SELECT uid FROM USER GROUP BY uid HAVING COUNT(uid) > 1) AND id NOT IN (SELECT MIN(id) FROM USER GROUP BY uid HAVING COUNT(uid) > 1)) AS u);
一开始直接使用以下语句删除,报错:You can’t specify target table 'user’ for update in FROM clause
DELETE FROM USER WHERE id IN (SELECT id FROM USER WHERE uid IN (SELECT uid FROM USER GROUP BY uid HAVING COUNT(uid) > 1) AND id NOT IN (SELECT MIN(id) FROM USER GROUP BY uid HAVING COUNT(uid) > 1));
查资料后得知:
因为在 MYSQL 里,不能先 select 一个表的记录,在按此条件进行更新和删除同一个表的记录。
解决办法:
将 select 得到的结果,再通过中间表 select 一遍。
SQL如下:
DELETE FROM USER WHERE id IN (SELECT u.id FROM (SELECT id FROM USER WHERE uid IN (SELECT uid FROM USER GROUP BY uid HAVING COUNT(uid) > 1) AND id NOT IN (SELECT MIN(id) FROM USER GROUP BY uid HAVING COUNT(uid) > 1)) AS u);
联系客服