asked
How to update multiple table as a single transaction
how do I update several tables with columns that store social security number as a single transaction? I have over 68 tables to update the ssn with correct ssn.
How are you driving the update? Do you have a master table that will overwrite all other tables? How are you linking the tables?
The update is strictly by request. Occasionally, I get calls for a customer who entered their ssn wrongly the first time they registered on the our website. when I get such a request, I manually update all the ssn in every database table that stored information about the customer. A very tedious task to update over 70 tables with ssn columns one by one. Does anyone have an idea how to do this efficiently ?
From an update perspective, it would be more efficient to store the SSN in one table then reference that table from the 70 others. A single place to update. Since that ship has undoubtedly sailed, you might write a stored procedure that updates the 70 tables. Write it so you pass in the old SSN and the new SSN then it updates the 70 tables accordingly. Wrap the 70 updates within a single transaction and commit/rollback all together when the batch is complete. Are any of these used as foreign keys? If so, do you use on update cascade? That might get you part way there.
From an update perspective, it would be more efficient to store the SSN in one table then reference that table from the 70 others. A single place to update. Since that ship has undoubtedly sailed, you might write a stored procedure that updates the 70 tables. Write it so you pass in the old SSN and the new SSN then it updates the 70 tables accordingly. Wrap the 70 updates within a single transaction and commit/rollback all together when the batch is complete. Based the question's comment chain and a bit of speculation, here's a rough outline: create procedure UpdateSSN @oldSSN char(9), @newSSN char(9) AS begin tran begin try update table1 set SSN = @newSSN where SSN = @oldSSN update table2 set SSN = @newSSN where SSN = @oldSSN ... 65 more ... update table68 set SSN = @newSSN where SSN = @oldSSN commit tran end try begin catch rollback end catch
17 People are following this question.
