CODEWITHSUNDEEP
mysqlsql
beary605
beary605

Reputation: 824

"Merging" ids together in SQL

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.

Upvotes: 1

Views: 435

Answers (1)

blabla_bingo
blabla_bingo

Reputation: 2162

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.

Upvotes: 2

Related Questions