I have a table companies in which there are some duplicated rows I found with a simple GROUP BY name.

This companies table has a PRIMARY KEY id which is referenced in several other tables, under the name company_id, which I also found with a query on the INFORMATION_SCHEMA.

Now my requirement: Merge the two companies into one, meaning update all the referenced company_id to reference the first id of the two, then delete the second company

All the FOREIGN KEYS are already declared with ON UPDATE CASCADE clause but I’m not sure if there’s a smart way of accomplishing the final result without manually updating all the referenced FOREIGN KEYS

My intuition was to

• create a new temporary field mark_to_delete and set this = 1 on all the duplicated companies I want to delete
• use FOREIGN_KEY_CHECKS=0 to avoid errors while updating a company.ID to an existing one
• Delete companies that are mark_to_delete=1
• Turn back on FOREIGN_KEY_CHECKS=1

Clearly my biggest doubt is on how FOREIGN_KEY_CHECKS works, I’ve tried reading the docs and searching for my specific case, but I can’t understand if the ON UPDATE CASCADE will work or not.

Worst case scenario I think would be to list all the referenced company ID’s, run updates on the tables with a referenced ID setting it to the “healthy” company, and then delete the duplicated company.