Re: SQL. Correlated UPDATE problem.

Ivan Nilsson wrote:

> This is a simplified version of my problem. I have these two tables
>
> Customers
> Cusno, cusname
> 1, Johhny
> 2, Amanda
> 3, Anthony
> 4, Elsabeth
>
> Customers_newinfo
> Cno, Cname
> 2, Amanda Smith
> 3, Andy Welsh
>
> I wish to update Customers with the data from Customers_newinfo using one UPDATE statement.
> Thus, customer 2 in Customer shall have the name changed to Amanda Smith, and Customer 3 to Andy Welsh.

here we are!

update Customers cus

    set cusname = ( select Cname from Customers_newinfo cusInfo

        where cus.Cusno = cusInfo.Cuno) where cus.Cusno = ( select Cuno from Customers_newinfo cusInfo

        where cus.Cusno = cusInfo.Cuno)

>
>
> My problem is to make the UPDATE-statement correlate the customer numbers from the two tables.
>
> Anyone who knows how to do this?
>
> /TIA
Received on Thu Feb 03 2000 - 03:27:52 CST