Mysql delete the duplicate records, leave only one record

2019-09-1919:01:40 Comment

Here I will give a good SQL statement, it can delete the duplicate records and leave the min id record.

DELETE FROM student where id IN (
    SELECT * FROM (
        SELECT id FROM status WHERE (stuno, stuname) IN (
            SELECT stuno,stuname FROM student GROUP BY stuno,stuname HAVING COUNT(1) > 1
        ) AND id NOT IN (
            SELECT MIN(id) FROM student GROUP BY stuno,stuname HAVING COUNT(1) > 1
        )
    ) AS stu_repeat_copy
);

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: