0

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!

2 Answers 2

1

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.

2
  • Jithin - which plan did you pick? Did you experiment with all of them? (Just curious.)
    – Rick James
    Feb 8, 2017 at 23:06
  • ALTER IGNORE TABLE foobar ADD UNIQUE (title, SID) Mar 18, 2017 at 4:40
1

Try this

DELETE n1
    FROM tablename n1, tablename n2
    WHERE n1.id > n2.id
      AND n1.exam = n2.exam
      AND n1.name = n2.name
      AND n1.subject = n2.subject

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.