MySQL query duplicate data

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);

Tags: MySQL SQL

Posted on Sat, 16 May 2020 11:25:22 -0400 by dyconsulting