Jump to content

Removing duplicates


tjmbc

Recommended Posts

How do I delete duplicate records that have unique keys?

 

For instance, in my table there is:

 

id | value1 | value2 | value3

1      a          b          c

2      a          b          c

 

I want to delete record 2 (or 1).

I suppose I should also mention that there are a few thousand records in the table.

Link to comment
https://forums.phpfreaks.com/topic/109289-removing-duplicates/
Share on other sites

CREATE TABLE tmp

SELECT * FROM tablename

GROUP BY val1,val2,val3

 

Then replace original table data with that from tmp

 

mysql> SELECT * FROM tests;
+----+------+------+------+
| id | val1 | val2 | val3 |
+----+------+------+------+
|  1 | a    | b    | c    |
|  2 | b    | c    | a    |
|  3 | a    | b    | c    |
|  4 | b    | c    | a    |
|  5 | d    | e    | f    |
|  6 | g    | h    | k    |
+----+------+------+------+
6 rows in set (0.00 sec)

mysql> CREATE TABLE tmp
    -> SELECT * FROM tests
    -> GROUP BY val1,val2,val3;
Query OK, 4 rows affected (0.17 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tmp;
+----+------+------+------+
| id | val1 | val2 | val3 |
+----+------+------+------+
|  1 | a    | b    | c    |
|  2 | b    | c    | a    |
|  5 | d    | e    | f    |
|  6 | g    | h    | k    |
+----+------+------+------+
4 rows in set (0.00 sec)

Link to comment
https://forums.phpfreaks.com/topic/109289-removing-duplicates/#findComment-560733
Share on other sites

backup original table first (just in case)

 

mysql_query ("TRUNCATE TABLE original");                // removes all records

mysql_query ("INSERT INTO original SELECT * FROM tmp");         // move from rmp to original table

Link to comment
https://forums.phpfreaks.com/topic/109289-removing-duplicates/#findComment-561620
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.