Deleting duplicate rows

Anon

Is there a way to delete rows in MySQL in which every field has the same value? For example,

    Field 1     Field2      Field3

Row1: A B C
Row2: A B C
Row3: A B C

If I use:

DELETE from tablename WHERE field1=A AND field2=B AND field3=C;

then all three rows are deleted. I wish to delete only two and keep one.

Thanks.

tasistro

I think this might work:

Run a sql like this (i'm asuming you have some unique field called ID)

SELECT @rows:=count(ID) from tablename WHERE field1=A AND field2=B AND field3=C;

don't fetch any data, then run this sql

DELETE from tablename WHERE field1=A AND field2=B AND field3=C LIMIT @rows-1;

Saludos
Gerardo

tomhath

The way I usually do it is to do a SELECT DISTINCT into a temporary table, DELETE everything from the real table, then INSERT everything from the temporary table into the real table.

Back everything up first
Consider adding an auto-incremented Primary key column to the real table when you repopulate it.

tasistro

I think that would take too much extra time .Depending on the way you store your temp table you could crash the dbase if you run out of resources.

Saludos
Gerardo

tomhath

He said all the fields were duplicated, I took that to mean he did not have a unique ID field (otherwise he could just delete using that column).

I also assumed there were many sets of duplicate rows, not just A, B, and C.

If the server is so close to running out of resources that making a copy of one table crashes it, he's probably going to have other problems running queries.

Anon

Thanks a lot! Your method worked perfectly. I made sure to add a primary key column to my original tables.

tasistro

It really doesn't matter what criteria he has to find duplicate rows. Making a copy of a table and then cleaning it up in not a good way to go. Just clean it up by deletes on the original.

You CAN do what you say, but it creates a bad programing habit. When you have a table over 1E6 rows and you make an image to it you could run out of resources. I'm not talking about RAM, I'm also speaking about disk space. Your site could stop accepting emails, logins or user uploads.

It is not a good practice, but I will not put a gun to either one's head.

Saludos
Gerardo