How to update values of foreign key fields referencing to a parent table in MySQL?

Question

I am migrating a table to a new format and as part of the process, I need to delete rows and condense them into a single row. However, these old rows are being referenced by other tables.

Is there a way to automatically update the values of the foreign keys referencing the old rows to the 'id' of the newly-created row or is this only possible manually going through all the referencing tables? I am using a number (id) to identify the rows.

The only other way possible is to go through each referencing table and update the old values with multiple sub-queries OR disable foreign key checks then delete the old rows, but this wouldn't update the values in child tables.

Error Message: SQL Error (1451): Cannot delete or update a parent row: a foreign key constraint fails... (table names I can't show)

Answer 1

I suggest you to create a mapping between the Old and New Ids first, and then temporary introduce a new field called NewId what you will populate from that mapping. Next, once you have populated all new Ids, you can simply update your primary Id from the new one and delete the NewId column completely.

Of course, before you start any key updates you will require to disable a foreign key constraint, read this post about how to do it.