How to remove duplicates from a MySQL table?

Question

Possible Duplicate:
Remove duplicates using only a MySQL query?

I have a table with different pages but some of them are duplicates. The only way to determine duplicates are by title. I run the code below and it works perfectly:

<?php

mysql_connect("localhost", "root", "root") or die(mysql_error());
mysql_select_db("old") or die(mysql_error());
$result = mysql_query("SELECT pagetitle, COUNT( * ) c
FROM cms_site_content
GROUP BY pagetitle
HAVING c >1") or die(mysql_error());  
while($row = mysql_fetch_array($result)){
    echo $row['id'].'-'.$row['pagetitle'].'<br />';
}

?>

My question is: How can I erase duplicates and keep only one entry. For example if I have an article called "Duplicate Article" and the result is

Duplicate Article: 3

I want to keep only one.

hi andrei, can you post the schema of your table and sample records? thanks. it helps a lot when asking question :) — John Woo, Aug 10 '12 at 15:45
Please, don't use `mysql_*` functions for **new** code. They are no longer maintained and the community has begun the [deprecation process](http://goo.gl/KJveJ). See the [**red box**](http://goo.gl/GPmFd)? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide, [this article](http://goo.gl/3gqF9) will help to choose. If you care to learn, [here is a good PDO tutorial](http://goo.gl/vFWnC). — PeeHaa, Aug 10 '12 at 15:46
Isn't this something you can do by hand on the database table? If not, what's the criteria for deleting a duplicate page? — j08691, Aug 10 '12 at 15:46

Daniel Li · Accepted Answer · 2012-09-27T03:45:47.043

8

You'll want to INNER JOIN the table on itself and delete where the pagetitle values are identical but the primary key (I'm using ID as the identifier) is not.

Try:

DELETE c2 FROM `cms_site_content` c1 INNER JOIN
               `cms_site_content` c2
          ON c1.pagetitle = c2.pagetitle AND c1.ID <> c2.ID

Reference: http://dev.mysql.com/doc/refman/5.0/en/join.html

edited Sep 27 '12 at 03:45

answered Aug 10 '12 at 15:47

Daniel Li

14,976
6
43
60

Also reference: http://stackoverflow.com/a/3383920/62671 – Matt Razza Aug 10 '12 at 15:49
I've used this code: c2.id") or die(mysql_error()); ?> and it gives me: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM 'cms_site_content' AS c1 INNER JOIN 'cms_site_content' AS c2 ON m1.pageti' at line 1 – Andrei RRR Aug 10 '12 at 15:57
I've used this: DELETE FROM 'cms_site_content' AS c1 INNER JOIN 'cms_site_content' AS c2 ON m1.pagetitle = m2.pagetitle AND c1.id <> c2.id (without an *). Got this: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS c1 INNER JOIN cms_site_content AS c2 ON m1.pagetitle = m2.pagetitle AND c1.id' at line 1 – Andrei RRR Aug 10 '12 at 16:02
'#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'c1 INNER JOIN cms_site_content c2 ON c1.pagetitle = c2.pagetitle AND c1.ID <> c2' at line 1' - This was made directly in phpMyAdmin – Andrei RRR Aug 10 '12 at 16:06
1

DELETE c2 FROM... was the right code :) – Andrei RRR Aug 10 '12 at 16:37

score 0 · Answer 2 · answered Aug 10 '12 at 15:47

0

Select all single rows, insert them into a temp table, delete all record, insert them back from temp

Use some application logic: select only one from each multiple row, delete all from the table, then insert back the single ones

answered Aug 10 '12 at 15:47

Peter Kiss

9,309
2
23
38

score 0 · Answer 3 · answered Aug 10 '12 at 15:51

0

Try this: SELECT DISTINCT pagetitle, id, FROM cms_site_content

Save all ids to idlist use PHP from above SQL and then execute:

DELETE FROM cms_site_content WHERE id NOT in ($idlist)

You need to translate above SQL statements to PHP/MySQL code.

answered Aug 10 '12 at 15:51

Frank He

536
3
9

You can do this without PHP in pure SQL. – nkr Aug 10 '12 at 15:51
Yes, the other posts are simpler – Frank He Aug 10 '12 at 15:52

score 0 · Answer 4 · answered Aug 10 '12 at 16:32

0

This line woked:

DELETE t2
FROM cms_site_content t1
JOIN cms_site_content t2 ON (t2.pagetitle = t1.pagetitle AND t2.id > t1.id);

answered Aug 10 '12 at 16:32

Andrei RRR

3,068
16
44
75

score -2 · Answer 5 · answered Aug 10 '12 at 15:49

-2

try this

DELETE t1 FROM table t1, table t2 WHERE t1.id > t2.id AND t1.name = t2.name

answered Aug 10 '12 at 15:49

WatsMyName

4,240
5
42
73

How to remove duplicates from a MySQL table?

5 Answers5