"Merging" ids together in SQL

Question

I have a table cities like this:

id	city
1	Vancouver
2	Calgary
3	Calgry

And multiple other tables which reference cities, something like this (just some example numbers).

id	city_id	year	population
1	1	2000	100000
2	1	2001	130000
3	3	2000	70000
4	3	2001	85000
5	2	2002	95000

I want to merge/consolidate city id 3 into id 2, so change every occurrence of id 3 across all tables to 2, and then delete city id 3. Is there a clean way of doing this?

Something like ON UPDATE CASCADE would work perfectly but I can't have duplicate primary ids. At the very least I could loop through the foreign keys and run a query on every table but I'm not sure if there's a more idiomatic way.

Answer 1

ON UPDATE CASCADE causes duplicate PK. To avoid that, we can use ON DELETE SET NULL which nullify all the city_id with a value 3 in other tables once we delete the city_id 3 from the cities table, but then you would still have to change the city_id from null to 2 in the said tables. This is not fundamentally different to change those city_id with a value 3 to 2 for the other tables , then just delete id 3 from the cities table. If you feel reluctant to manually do that or if there are too many tables to handle, then use a procedure. Make a list of table , declare a cursor for the list and loop through the list to get each table name and use a prepared statement to do the UPDATE.