Re: Delete duplicate rows
Make a temporary table, SELECT DISTINCT the rows from the old table to the new table, rename the old table TABLE_OLD, rename the temporary table to the original table name. Peter ^_^ - Peter Grigor Hoobly Free Classifieds http://www.hoobly.com - Original Message - From: Lewis Watson [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Saturday, February 15, 2003 12:30 AM Subject: Delete duplicate rows 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 - 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 - 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
Re: Delete duplicate rows
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
Delete duplicate rows
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 - 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
