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;
