lets say you have a table of names and you want to remove all the duplicate names select count(*) AS num,name FROM names WHERE count(*) > 1 GROUP BY name Then, in your script (php, perl, whatever) you do DELETE FROM names WHERE name = '$name' LIMIT ($num-1) For all the rows returned by the first query I don't know if there's a way to do that all in a single query
You really can't do it with one query. You'll have to use a Temporary table, grab all your data, and then delete based on that..
Lots of ways to do this, including using 1 sql statement. You need to use rowid here. This points to the location of the record on disk. Two rows with exactly same data wil have different rowid's. For each row, keep the record with the minimum row_id. In order to prevent duplicates, put a proper key on the table.
I do this all time time. Are the rows exactly duplicate, or is there a column or two that are different? If they are true duplicate, and all columns are exactly the same, the using the min(row_id) is an excellent suggestion. Something like... SELECT MIN(ROW)ID), COLUMN_1, COLUMN_2, COLUMN_3... FROM TABLE1 GROUP BY COLUMN_1, COLUMN_2, COLUMN_3... If all columns are not exactly the same, then you'll need to take a close look at the columns that have different values, and decide which ones you want to keep.