SQL query for deleting duplicate records in parent table without affecting child table

We need to take additional care when deleting duplicate rows in a table if the table is related with another table and there are no proper foreign key constraints available.

Consider below two tables. The parent table is having duplicate rows. If we just blindly delete the duplicate rows in the parent table, the child table rows associated with the duplicate parent row will be in trouble.
So to avoid this issue we need to update the child rows with available parent row ids once after removing duplicate rows in the parent table. Anyway the best way is defining appropriate foreign key constraints.

Find below the DELETE query for deleting duplicate rows in the parent table and UPDATE query for making necessary changes in child table once after executing the DELETE query.



Delete
from parent_table
where id not in
(select min(id) from parent_table P2 where P2.Name=parent_table.Name)

update parent_table p,child_table c set c.parent_id=p.id
where c.parent_id not in
(select c.parent_id from child_table c, parent_table p where c.parent_id=p.id)
More Articles...