Solved

SQL Script with temp tables etc

Posted on 2011-09-27
2
190 Views
Last Modified: 2012-05-12
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
Comment
Question by:red_75116
  • 2
2 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36711372
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
 
LVL 21

Accepted Solution

by:
JestersGrind earned 500 total points
ID: 36711513
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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now