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;
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
