question

zillabaug avatar image
zillabaug 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.
sql-server-2005sql-server-2000stored-procedurestsqlcursor
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

How are you driving the update? Do you have a master table that will overwrite all other tables? How are you linking the tables?
0 Likes 0 ·
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 ?
0 Likes 0 ·
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.
1 Like 1 ·
Thank you kenJ. Can.Can you give me a blueprint to follow? My SQL is not that strong :).
0 Likes 0 ·
Just curious, are all of these tables within the same database? Or are they in different databases?
0 Likes 0 ·
All the tables are in the same database
0 Likes 0 ·

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.