Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

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?
0
red_75116
Asked:
red_75116
  • 2
1 Solution
 
JestersGrindCommented:
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

0
 
JestersGrindCommented:
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

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now