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)
Add a comment
|
1 Answer
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
)
7 Comments
K Arun Singh
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
nikita
thanks :) @K Arun Singh
theberzi
@KArunSingh it would have been nice if you had shared your solution...
theberzi
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)?
Shira Ishikawa
@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.
|