Link to home
Start Free TrialLog in
Avatar of red_75116
red_75116Flag for United States of America

asked on

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?
Avatar of JestersGrind
JestersGrind
Flag of United States of America image

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

ASKER CERTIFIED SOLUTION
Avatar of JestersGrind
JestersGrind
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial