List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 15 2003 3:56pm
Subject:Re: Delete duplicate rows
View as plain text  
At 23:30 -0600 2/14/03, Lewis Watson wrote:
>I need to delete duplicate rows. Each row that is in the table has an
>exact duplicate of itself. There are four columns. No one column could be
>defined as a primary key; however, two columns together could. What's
>going to be the best way to do this?
>Thanks.
>Lewis
>
>mysql, thanks

You can define those columns as a primary key with ALTER IGNORE TABLE
and MySQL will remove the duplicate rows with duplicate primary key values.
(The IGNORE is important, otherwise the statement will fail.)

Here's an example that shows how it works.

mysql> CREATE TABLE t (i INT NOT NULL, j INT NOT NULL);
mysql> INSERT INTO t (i,j) VALUES(1,1);
mysql> INSERT INTO t (i,j) VALUES(1,1);
mysql> INSERT INTO t (i,j) VALUES(1,2);
mysql> INSERT INTO t (i,j) VALUES(1,2);
mysql> INSERT INTO t (i,j) VALUES(2,2);
mysql> SELECT * FROM t;
+---+---+
| i | j |
+---+---+
| 1 | 1 |
| 1 | 1 |
| 1 | 2 |
| 1 | 2 |
| 2 | 2 |
+---+---+
mysql> ALTER IGNORE TABLE t ADD PRIMARY KEY (i,j);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 2  Warnings: 0
mysql> SELECT * FROM t;
+---+---+
| i | j |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 2 | 2 |
+---+---+

If you don't want to keep the index, drop it:

mysql> ALTER TABLE t DROP PRIMARY KEY;
Thread
Delete duplicate rowsLewis Watson15 Feb
  • Re: Delete duplicate rowsPeter Grigor15 Feb
  • Re: Delete duplicate rowsPaul DuBois15 Feb