How to delete all but one row in SQL

Question

I would like to delete everything except one line, but in combination with PHP.

I think this will be a simpler solution to my problem, which is to delete duplicate information.

Okay ... I just realized what I already did is shit.

Basically, I have a table (dev_discs), and I want to look for duplicates in the "discReference" column, and then delete all but one, leaving one remaining row.

Is there a simple query for this? I came across similar situations on the net, and they are all complicated ... I do not think this is a very complicated scenario.

Answer 1

Not easy, AFAIK. With PHP, this should not be too complicated (I don’t think you can easily do this just by using SQL).

• Create a new table with the same structure as the original:

  CREATE TABLE dev_discs_noduplicates (...);

• Extract all non-duplicated data from the source table with the GROUP clause to eliminate duplicates:

  SELECT * FROM dev_discs WHERE 1 GROUP BY dupe_col1, dupe_col2;

• Scroll through the results and paste them into a new table:

  INSERT INTO dev_discs_noduplicates ...;

• Delete old table

  DROP TABLE dev_discs;

• Rename the new table:

  RENAME TABLE dev_discs_nodulicates TO dev_discs;

[EDIT]

Since @xanatos rightfully noticed a possible loss of relations in the database, here is an alternative solution that includes SQL and PHP.

First select the unique lines:

SELECT id FROM dev_discs GROUP BY col1, col2; 

Having them in a PHP array, insert it and use it in the delete request:

 DELETE FROM dev_discs WHERE (id) NOT IN ( @arr ); 

This should take care of all possible problems.

Answer 2

 DELETE FROM discs dd WHERE EXISTS ( SELECT * FROM discs d2 WHERE d2.discReference = dd.discReference AND d2.rownumber < dd.rownumber ); 

"rownumber" is the internal identifier that is provided by your DBMS. You can call tid, oid.