In a MySQL database, I have a table full of business information. Lots of businesses have been inserted twice due to human error, so the same information exists with two different primary keys. Let's say the same delivery service is represented by primary key 3 and 7.
Businesses: | id | name | | 3 | Planet Express | | 7 | Planet Express |
Lots of other tables foreign key to this business table. (Employees, etc)
Employees: | id | name | business | | 999 | Fry | 3 | | 666 | Bender | 7 |
Fry and Bender really do work for the same business. I'd like to delete the business row with primary key 7, and tell all the foreign key tables that instead of cascade delete, or cascade null, they should update to point to primary key 3.
Obviously I could do this by hand, but I'd rather make the database do it on my behalf since it has a better memory for this kind of thing. Is there a way to do this? Like
DELETE FROM business WHERE id = 7 ON CASCADE set 3; or something?