Re: getting rid of duplicates

[Christian Mack]

Joel Bremson wrote:
> 
> Is there a simple way to remove exact duplicate rows from the database?
> 
> Joel3

Hi Joel

You have two choices:
1) If your row is less than 255 bytes wide and contains only up to 16 columns, then you
can create a UNIQUE key over all columns with:
ALTER TABLE tablename ADD UNIQUE (col1, col2, col3, ...);

2) Create a temporary table with the exact same column names and types as the table you
want to be unique.
Then insert the values from the original table into the temporary one with the IGNORE
option.
Finally DROP the original table and rename the temporary table to the original table name.
Example:
LOCK TABLES tempOrig WRITE, original WRITE;
CREATE TABLE tempOrig (col1 INT, col2 VARCHAR(20),col3 TEXT,...);
INSERT IGNORE INTO tempOrig SELECT * FROM original;
DROP TABLE original;
ALTER TABLE tempOrig RENAME original;
UNLOCK TABLES;


Tschau
Christian