MySQL – Remove Second Repetition of Combination

MySQLunique-constraintupdate

enter image description here

I have a datable like above and in that, unfortunately, repetitions occurs. How can I remove the repeated combination of 2nd, 3rd and 4th column and I need to make those three columns unique in combination.

Please help to write a query to do this, because there are 13000+ rows, so manual editing is hardly not possible.

Thanks in advance!

Best Answer

When finished, be sure to add this so dup rows won't recur:

UNIQUE(exam, name, subject)

Plan A:

For that query (and assuming it does the desired task), it would help to have INDEX(exam, name, subject, id).

Suggest you test it in a safe way... Create another table and try it there.

CREATE TABLE test LIKE tablename;
ALTER TABLE test ADD INDEX(exam, name, subject, id);
INSERT INTO test
    SELECT * FROM tablename;
DELETE ...
...look at results...

If not right, DROP TABLE test and try again.
If looks good, then finish by moving the good result into place:

RENAME TABLE tablename TO old,
             test to tablename;
DROP TABLE old;

Plan B:

CREATE TEMPORARY TABLE tmp
    SELECT MIN(id) AS id FROM tablename
        GROUP BY exam, name, subject;
DELETE tablename
    FROM tablename
    LEFT JOIN tmp  USING(id)
    WHERE tmp.id IS NULL;

(Again, you should probably test it on a copy.)

Plan C: Even simpler, assuming you don't care what data you get for the extra columns:

CREATE TABLE new
    SELECT * FROM tablename
        GROUP BY exam, name, subject;
... check table `new` ...
RENAME TABLE tablename TO old,
             new to tablename;
DROP TABLE old;

That is, it won't necessarily give you the first id. You may need a particular setting to turn off "only full group by" if you have a 'new' version of MySQL.

Related Question