There are two solutions to this:
[A] You can use the following construct:
SET c.CustID = nc.NewCustID
Cust c INNER JOIN NewCust nc ON
c.CustID = nc.CustID
This will work only if there is a 1-1 relation, otherwise only the first
record from the set of records will be picked up.
[B] You can also use the following construct
SET CustID = (SELECT nc.NewCustID FROM NewCust nc WHERE nc.CustID =
This will work only if there is a 1-1 relation, otherwise an error will be
For your other question, you can probably iterate thorugh the columns of all
the tables and see if CustID is present and if so, execute a similar
statement as above using dynamic SQL.
Please reply to the whole group only!
"Helen Berg" <helen_bergethotmail.com> wrote in message
> I have two tables
> Cust and NewCust
> In Cust i have the CustID stored
> In NewCust i have the CustID and NewCustID stored
> I would like to uppdate the CustID in Cust with
> the NewCustID in NewCust where Cust.CustID=NewCust.CustID
> I think my brain left for vaccation to early...just cant
> get it to work.
> Question 2
> Is there a way to perform this agains all Tables in the
> database where CustID exists without running it once for
> each table and get some kind of output log of where and
> which CustID was updated?