SQL Script with temp tables etc

I am trying to write a SQL update query which will loop through several tables and update values.

I have a cus_no in several tables and I want to create a temporary table in SQL with the old cus_no and the new cus_no value and have loop through a list of tables and change the values in each of those tables.

Example
temptable
old_cus | new_cus
A1 | B1
A2 | B3
A3 | C1
A4 | D47

and have it loop through tables like

invoicetbl.cus_no
addresstbl_cus_no
phonetbl_cus

So it would change the values in each of those tables to the new values from the temptable.
Is that possible?

red_75116

Asked: 2011-09-27

Advertise Here

1 Solution

LVL 21

JestersGrindCommented: 2011-09-27

Is cus_no part of any foreign key relationships in these tables? If so, it gets a little more complicated. Either way, there is no need to loop. You can join to the temp table and update all the values at once. This would be the set based approach which is more efficient than looping.

Greg

LVL 21

JestersGrindCommented: 2011-09-27

If it is part of the foreign key, you won't be able to update the values. You will have to insert records with the new values and then delete the old records. You also need to make sure you insert the parent records first and delete the child records first.

Greg

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Expand your skills with this month's free Premium course.

SQL Script with temp tables etc

Join & Write a Comment Already a member? Login.

Featured Post