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).
Produce a new table with similar structure because the original one:
CREATE TABLE dev_dvds_noduplicates ( ... )
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
Iterate within the results and place these to the brand new table:
Place INTO dev_dvds_noduplicates ...
Remove old table
DROP TABLE dev_dvds
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.
DELETE FROM discs dd WHERE EXISTS ( 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.