Removing duplicates

[tjmbc]

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.

[Barand]

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)

[tjmbc]

That worked perfect, but forgive me for saying I'm not sure how to replace the original table with the 'tmp' one. Anyone?

[Barand]

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