Deleting duplicates from a MySQL database with a single duplicate colum

Question

So I have read through a lot of Stack Overflow questions on this... and I really couldn't get my answer :(. I've only been working with MySQL for about 4 months, so I am not exactly a pro.

Anyways, I have data in my table that looks something similar to this...

id | user | amount

1 | dillyg10 | 16
2 | dillyg10 | 18

As you can see, the user field is dupped. This happens a lot in my data set, although my table is very small, only about 1000 rows.

So if you guys could give me some advice... I would really appreciate it, also go through what you do in the query, I'm trying to learn and hopefully I won't have to ask a question like this again :).

What record out of two that you showed you want to delete? They have different `amount` value. What do you want to do about it? Or you just don't care which record stays? — peterm, Oct 01 '13 at 03:57

score 0 · Answer 1 · edited Oct 01 '13 at 04:21

0

You can use below query to delete duplicate record.

DELETE FROM test_table
      WHERE ID IN (SELECT   MAX (ID)
                       FROM test_table
                   GROUP BY user);

edited Oct 01 '13 at 04:21

hkf

4,440
1
30
44

answered Oct 01 '13 at 04:02

SeeSharp

179
1
4
12

2

This is a solution if there are exactly two repeating records for each user. If there are more and should be one left, then changing 'where id in' to 'where id not in' will do the task. – Alexey Oct 01 '13 at 04:08
1

I have attempted to do this, and I get the following error: You can't specify target table 'testingdupes' for update in FROM clause. – dillyg10 Oct 01 '13 at 04:28

peterm · Answer 2 · 2013-10-01T04:55:22.783

If you want to delete all but a row with a maximum id out of duplicate rows per user then you can do it like this

DELETE t 
  FROM table1 t JOIN
(
  SELECT user, MAX(id) id
    FROM table1
   GROUP BY user
  HAVING COUNT(*) > 1
) q 
   ON t.user = q.user
  AND t.id <> q.id

Here is SQLFiddle demo

If on the other hand you want to leave rows with maximum amount out of duplicate rows

DELETE t 
  FROM table1 t JOIN
(
  SELECT i.id, i.user, i.amount
    FROM
  (
    SELECT user, MAX(amount) amount
      FROM table1
     GROUP BY user
    HAVING COUNT(*) > 1
  ) p JOIN table1 i
      ON p.user = i.user
     AND p.amount = i.amount
) q 
   ON t.user = q.user
  AND t.amount <> q.amount
   OR (t.amount = q.amount
       AND t.id < q.id)

Here is SQLFiddle demo

@user872129 Did it help after all? – peterm Oct 01 '13 at 20:29 — peterm, Oct 01 '13 at 20:29

Deleting duplicates from a MySQL database with a single duplicate colum

2 Answers2