x

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.

more ▼

asked Mar 30, 2015 at 04:03 PM in Default

avatar image

zillabaug
481 3 7 15

How are you driving the update? Do you have a master table that will overwrite all other tables? How are you linking the tables?

Mar 30, 2015 at 06:15 PM iainrobertson

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 ?

Mar 30, 2015 at 06:29 PM zillabaug

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.

Mar 30, 2015 at 06:51 PM KenJ

Thank you kenJ. Can.Can you give me a blueprint to follow? My SQL is not that strong :).

Mar 30, 2015 at 07:24 PM zillabaug

Just curious, are all of these tables within the same database? Or are they in different databases?

Mar 30, 2015 at 08:06 PM JohnM

All the tables are in the same database

Mar 30, 2015 at 08:08 PM zillabaug
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Mar 31, 2015 at 07:23 PM

avatar image

KenJ
25k 3 13 20

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2036
x512
x479
x454
x70

asked: Mar 30, 2015 at 04:03 PM

Seen: 1306 times

Last Updated: Mar 31, 2015 at 09:11 PM

Copyright 2018 Redgate Software. Privacy Policy