10

I want to delete the extra duplicate record

i.e. in the image shown there are two records with corporate_id = 5 and category_id = 19, how to delete any one row which is duplicate (here corporate_sed_id is the primary key)

enter image description here

K Arun Singh
  • 2,629
  • 3
  • 20
  • 34

1 Answers1

15

Use this :-

DELETE
FROM
    corporate
WHERE
    corporate_sed_id IN(
    SELECT
        *
    FROM
        (
        SELECT
            MIN(corporate_sed_id)
        FROM
            corporate
        GROUP BY
            corporate_id,
            category_id
        HAVING
            COUNT(corporate_sed_id) > 1
    ) temp
)
nikita
  • 404
  • 3
  • 8
  • 1
    I have solved this problem through different way, but just to check your solution I have executed this query and this worked like a charm :) Thank you @nikita – K Arun Singh Oct 03 '17 at 12:13
  • thanks :) @K Arun Singh – nikita Oct 03 '17 at 12:28
  • Perfect solution, thanks ! – Keytrap Dec 16 '20 at 14:08
  • @KArunSingh it would have been nice if you had shared your solution... – theberzi Jun 10 '21 at 07:10
  • 3
    This will only remove one duplicate if there are several with the same id, right? One would have to run it multiple times to remove all duplicates. Is there a way to make it work for any number of duplicates (i.e. only keep one)? – theberzi Jun 10 '21 at 07:14
  • It's also useless if you don't have an id, like in a pivot table – Tofandel Jul 08 '21 at 08:45
  • 1
    @theberzi you can switch the IN into NOT IN. if you run the select * it will return the smallest corporate_sed_id from duplicates, means if you want to left only one from many duplicates, NOT IN will do the trick. Although you also need to check again the HAVING condition in case you have mixed data with no duplicates. – Shira Ishikawa Mar 07 '22 at 12:20
  • It worked, however for a huge dataset I noticed it does not delete all the duplicates at once, you have to run it several times as it returns a number of affected rows. A good suggestion on this would be, to run this statement in a **PHP loop** until it returns 0 rows. – JOB May 20 '22 at 10:24