SQL Script: Updating a column with another table pivoting on an ID

Question

I have two SQL Server tables: ORDERS and DELIVERIES.

I would like to update the ORDERS table with a value from DELIVERIES. The ORDERS PK (OrderID) is common to both tables. Also, I would like to restrict the action to a specific CustomerID (within ORDERS).

ORDERS table:

OrderID | AccountID | AnalysisField1

DELIVERIES table:

DeliveryID | OrderID | AddressName

I want to update ORDERS.AnalysisField1 with the value from DELIVERIES.AddressName (linked by OrderID) but only where ORDERS.AccountID = '12345'

Please help. JM

Answer 1

Then try to use something like this:

UPDATE dbo.Orders
SET AnalysisField1 = d.Addressname
FROM dbo.Deliveries d
WHERE
    d.OrderID = dbo.Orders.OrderID
    AND dbo.Orders.AccountID = '12345' 

If your AccountID column is of a numerical type (which the ID suffix would suggest), then you should not put unnecessary single quotes around the value in the WHERE clause:

    AND dbo.Orders.AccountID = 12345