I must remove basically one row but along with PHP.

It is a simpler solution for my problem, which would be to remove duplicate information.

Ok... I simply realized what i have already done is garbage.

Essentially i've got a table (dev_dvds) and i wish to look for replicates within the column 'discReference' after which remove basically one, thus departing the main one remaining row.

Can there be a simple query to get this done? I discovered the same situation around the internet and they are all complicated... I do not think its a really complicated scenario.

Very difficult way, AFAIK. With PHP it should not be too hard though (I'm able to think about not a way this can be done easily simply using SQL).

  1. Produce a new table with similar structure because the original one:

    CREATE TABLE dev_dvds_noduplicates ( ... )

  2. Fetch all non-copied data in the original table having a GROUP clause to get rid of replicates:

    Choose * FROM dev_dvds WHERE 1 GROUP BY dupe_col1, dupe_col2

  3. Iterate within the results and place these to the brand new table:

    Place INTO dev_dvds_noduplicates ...

  4. Remove old table

    DROP TABLE dev_dvds

  5. Relabel new table:

    Relabel TABLE dev_dvds_nodulicates TO dev_dvds


Since @xanatos rightfully remarked a potential relations reduction in the database, here's a different including SQL and PHP.

First, fetch unique rows:

SELECT id FROM dev_discs GROUP BY col1, col2;

Getting these inside a PHP array, implode it and employ within the remove query:

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

This will take proper care of all of the possible issues.

    ( SELECT * FROM discs d2
     WHERE d2.discReference = dd.discReference
     AND d2.rownumber < dd.rownumber

"rownumber" is definitely an internal id, that is provided because of your DBMS. Might be known as tid, oid.