deleting duplicate entries in MySQL

[webguync]

I have a problem with a MySQL submitting app which created duplicate data in the MySQL table. I need to go in and delete the dups, and right now I have just been going into PHPMyAdmin and deleting one at a time which is a painfully slow process. is there a SQL query to accomplish this faster? Keep in mind, I don't want to delete everything. Just the duplicate entries.

 

TIA

[Ken2k7]

Here's a sample.

DELETE t1
FROM tablename t1
INNER JOIN tablename t2
WHERE t1.column_name = t2.column_name
     AND t1.id <> t2.id;

[webguync]

thanks for the reply. These dups are (were) all in one table though.

[Ken2k7]

Yeah I know. You can join the same table.

[webguync]

say the column first names and last names are the same, but the id column auto increments so is different. How would I need to structure that? Not sure that to put in the place of t1 and t2 in your example.

 

thanks

[EchoFool]

His example is right if you put the same table name for each ti and t2

[webguync]

hmmmm,

I just tried a test and it didn't delete anything.

 

in the table test_mysql, I have two entries both with the same first name and lastname, My columns are id,name,lastname and email. The id auto increments, so is different for each entry.

 

here is the sql I tried.

 

DELETE t1

FROM test_mysql t1

INNER JOIN test_mysql t2

WHERE t1.name = t2.name

AND t1.id <> t2.id;

[Ken2k7]

It should work then. Here's a test run I used:

 

mysql> SELECT * FROM foo;
+----+------+------+---------+
| id | col1 | col2 | user_id |
+----+------+------+---------+
|  1 |   20 |   20 |       0 | 
|  2 |   20 |   15 |       0 | 
|  3 |   15 |   30 |       0 | 
|  4 |   60 |   80 |      50 | 
|  5 |   32 |    9 |      12 | 
|  6 |   20 |    3 |       5 | 
+----+------+------+---------+
6 rows in set (0.00 sec)

mysql> DELETE f1 FROM foo f1 INNER JOIN foo f2 WHERE f1.col1 = f2.col2 AND f1.id <> f2.id;
Query OK, 2 rows affected (0.01 sec)

mysql> SELECT * FROM foo;
+----+------+------+---------+
| id | col1 | col2 | user_id |
+----+------+------+---------+
|  1 |   20 |   20 |       0 | 
|  3 |   15 |   30 |       0 | 
|  4 |   60 |   80 |      50 | 
|  5 |   32 |    9 |      12 | 
+----+------+------+---------+
4 rows in set (0.00 sec)

[webguync]

ok, I see what you did. That worked when I tried your example. My situation though is going to be duplicate rows not columns.

 

for example.

 

id    firstname    lastname  email

1    Bob          Jones        [email protected]

2    Steve        Smith        [email protected]

3    Jake          Thomas    [email protected]

4    Bob            Jones        [email protected]

 

 

[fenway]

You can use DISTINCT for that.

[webguync]

Would I still need to do the inner join while using DISTINCT or no?

[fenway]

Would I still need to do the inner join while using DISTINCT or no?

DISTINCT is for SELECT only... so I'm not sure that I understand what you're asking.

[webguync]

I am just trying to delete multiple rows, instead of just checking one at a time and clicking on the X in PHPMyAdmin.

[fenway]

Well, if it's just a one-time thing, you can simply add a unique index across all three columns, and mysql will take care of it for you.

[artacus]

you can simply add a unique index across all three columns, and mysql will take care of it for you.

 

Really? I'd be very surprised if it did that. It SHOULD prevent you from creating a unique index while dups exist. I tested it and that is the behavior on the version I'm using (5.0)

 

You want to be careful that you don't delete both records. Here's what you can do:

 

-- To view your dups
SELECT first_name, last_name, phone, COUNT(1) AS cnt
FROM foo
GROUP BY first_name, last_name, phone HAVING COUNT(1) > 1;

-- to delete
DELETE bar FROM bar
JOIN (
    SELECT f_name, l_name, phone, MIN(id) min_id
    FROM bar
    GROUP BY f_name, l_name, phone HAVING COUNT(1) > 1
) sub ON bar.f_name = sub.f_name 
   AND bar.l_name = sub.l_name 
   AND bar.phone = sub.phone
WHERE bar.id <> sub.min_id

 

That leaves only the first entry (lowest id). Depending on your business needs, you may want to keep the latest entry using MAX instead.

[fenway]

you can simply add a unique index across all three columns, and mysql will take care of it for you.

 

Really? I'd be very surprised if it did that. It SHOULD prevent you from creating a unique index while dups exist. I tested it and that is the behavior on the version I'm using (5.0)

 

It will do it you ask for it explicitly.

 

ALTER IGNORE TABLE add unique index ( firstname, lastname, email )

 

will work this way -- I have made this suggestion many times.

[artacus]

Oh, just MySQL breakin the rules again.

[fenway]

Oh, just MySQL breakin the rules again.

Not really... if you ask it to ignore the errors, it will do as you say.