SQL - Delete all duplicate entries from table X

Question

I try to get a list of all UIDs from table fe_users which are redundant and then run my DELETE command to delete them.

e.g. I have this table (fe_users):

uid | pid | username
--------------
1   | 100 |  hans
2   | 100 |  karl
3   | 100 |  franz
4   | 100 |  karl
5   | 100 |  hans

This is how I select all duplicates:

SELECT * FROM fe_users
WHERE uid NOT IN (
    SELECT uid  
    FROM fe_users
    WHERE pid=100
    GROUP BY username
    HAVING COUNT(username) > 1
)
AND username IN (
    SELECT username 
    FROM fe_users
    WHERE pid=100
    GROUP BY username
    HAVING COUNT(username) > 1
)

Returns:

uid | pid | username
--------------------
5   | 100 |  hans
4   | 100 |  karl

Now I have to delete them.

Attempt #1:

(Hint: Just replaced SELECT from above Query with DELETE)

DELETE * FROM fe_users
WHERE uid NOT IN (
    SELECT uid  
    FROM fe_users
    WHERE pid=100
    GROUP BY username
    HAVING COUNT(username) > 1
)
AND username IN (
    SELECT username 
    FROM fe_users
    WHERE pid=100
    GROUP BY username
    HAVING COUNT(username) > 1
)

Response:

You can't specify target table 'fe_users' for update in FROM clause

Attempt #2

So I tried to use one more select, like suggested in this answer.

DELETE FROM fe_users
WHERE uid NOT IN (
    SELECT uid (
        SELECT uid
        FROM fe_users
        WHERE pid=100
        GROUP BY username
        HAVING COUNT(username) > 1
        )
    ) AS uid
AND username IN (
    SELECT username (
        SELECT username 
        FROM fe_users
        WHERE pid=100
        GROUP BY username
        HAVING COUNT(username) > 1
        )
    ) AS username;

Response:

Syntax error near 'SELECT uid FROM fe_users WHERE pid=0 GROUP BY usernam' at line 4

I don't know how I can do this and i wonder what the MySQL Developers where thinking by implementing this senseless restriction.

Do I have to delete it by using PHP?

Remove the `*` which is not in the query you show but the error message shows that you use it in your real query. A `delete` query has no `*` — juergen d, May 22 '17 at 11:51
Possible duplicate of [How to delete duplicates on a MySQL table?](http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-a-mysql-table) — rypskar, May 22 '17 at 11:52
Both of your subqueries are missing the from clause, i.e. you have `SELECT uid (SELECT uid FROM...`, it should be `SELECT uid FROM (SELECT uid FROM...`. That being said, Gordon's answer is much less verbose, and as such easier to read and maintain, with exactly the same end result. — GarethD, May 22 '17 at 11:59

score 3 · Accepted Answer · answered May 22 '17 at 11:53

Your query seems more complicated than necessary. I would go for:

delete u
    from fe_users u join
         fe_users u2
         on u.pid = u2.pid and u.username = u2.username and
            u2.uid > u.uid
    where u.pid = 100;

This deletes all rows where a row with the same pid and username exists with a larger uid. You can get the list by changing the delete u to select u.*.

Black · Answer 2 · 2017-05-22T13:00:02.213

My second attempt contained syntax errors as the error output stated. I missed FROM and there were some other errors. This is the working query:

DELETE FROM fe_users
WHERE uid NOT IN (
    SELECT uid FROM (
        SELECT uid
        FROM fe_users
        WHERE pid=100
        GROUP BY username
        HAVING COUNT(username) > 1
        ) As uid
    )
AND username IN (
    SELECT username FROM (
        SELECT username 
        FROM fe_users
        WHERE pid=100
        GROUP BY username
        HAVING COUNT(username) > 1
        ) AS username
    )
AND pid=100

I hope the MySQL developers are going to fix this, so that we don't need to add unnecessary SELECTS.

SQL - Delete all duplicate entries from table X

2 Answers2