asked on

SQL Update

Hello experts,

I need help with a query for updating. I have 2 tables, Customer and Customer_Order. I would like to create a mass update query that would allow me to take a new customer and replace it with an existing customer in Customer_Order.

DECLARE @SOURCE VARCHAR(30)
DECLARE @TARGET VARCHAR(30)

SET @SOURCE = '12344'
SET @TARGET = '39822'

UPDATE CUSTOMER_ORDER
SET NAME = X.NAME, ADDRESS = X.ADDRESS, CITY = X.CITY, STATE = X.STATE, ZIPCODE = X.ZIPCODE
FROM CUSTOMER X
WHERE X.ID = @SOURCE AND CUST_ID = @TARGET

So from above, I want to take all orders from customer 12344 and replace it with new customer 39822. Somehow it doesn't seem right. Can someone take a look and let me know how I can approach this?

johanntagle

I don't think you can do it in one SQL. I don't have syntax for SQL Server as I use mostly Oracle but the way I deal with such cases would be

select X.NAME, X.ADDRESS, X.CITY, X.STATE, X.ZIPCODE
into V_NAME, V_ADDRESS, V_CITY, V_STATE, V_ZIPCODE
FROM CUSTOMER X
WHERE X.ID = @SOURCE

then:

UPDATE CUSTOMER_ORDER
SET NAME = V_NAME, ADDRESS = V_ADDRESS, CITY = V_CITY, STATE = V_STATE, ZIPCODE = V_ZIPCODE
WHERE CUST_ID = @TARGET

Most of the time I have multiple sources, so I extract the name, etc using a cursor then loop around it.

G_Hosa_Phat

First thing I see is that there is no FROM clause in an UPDATE statement. It might be possible to do something like this, using an INNER JOIN statement, but I haven't tested it myself as I don't currently have a database to play with:

UPDATE (Customer_Order O INNER JOIN Customer X ON O.Cust_ID = X.ID)
SET O.Name = X.Name, O.Address = X.Address, O.City = X.City, O.State = X.State, O.ZIPCode = X.ZIPCode
WHERE X.ID = @SOURCE AND O.Cust_ID = @TARGET

Open in new window

ASKER CERTIFIED SOLUTION