Refactor Foreign Keys with Update after Primary Key change

Question

We ran into a problem with out primary key. It was set to a meaningful value for ease of data entry since all data was originally added directly. However now the meaningful value is not always present in all entries. So now we are moving to an auto-generated, non-meaningful key. But I have to update the database to reflect this.

So my products table has the columns serial (the original key) and Id (the new PK). My parts table has the 2 columns FK_serial (the old FK) and FK_product (the new FK, currently set to 0 for all entries).

Is there a UPDATE statement that will walk through the parts table and set the FK_product to the value of Id in the products table where serial = FK_serial?

Answer 1

UPDATE parts 
JOIN products
ON parts.FK_serial = products.serial
SET parts.FK_product = products.Id;