Suppose there is a user table, user. The data is as follows:
1. Duplicate uid data in query table
SELECT id, uid, name FROM USER WHERE uid IN (SELECT uid FROM USER GROUP BY uid HAVING COUNT(uid) > 1);
2. Duplicate data in the query table, excluding the minimum id
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);
3. Delete duplicate data in the table. If it is duplicate data, keep the one with the smallest id
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);
4. Problems encountered:
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));
After checking the data, we know:
Because in MYSQL, you cannot select the records of a table first, and update and delete the records of the same table according to this condition.
terms of settlement:
Select the result from select through the middle table again.
The SQL is as follows:
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);