Updating a sql server table with data from another table

Question

I have two basic SQL Server tables:

Customer (ID [pk], AddressLine1, AddressLine2, AddressCity, AddressDistrict, AddressPostalCode)

CustomerAddress(ID [pk], CustomerID [fk], Line1, Line2, City, District, PostalCode)

CustomerAddress contains multiple addresses for the Customer record.

For each Customer record I want to merge the most recent CustomerAddress record where most recent is determined by the highest CustomerAddress ID value.

I've currently got the following:

UPDATE Customer
SET 
  AddressLine1 = CustomerAddress.Line1,
  AddressPostalCode = CustomerAddress.PostalCode
FROM Customer, CustomerAddress
WHERE 
  Customer.ID = CustomerAddress.CustomerID

which works but how can I ensure that the most recent (highest ID) CustomerAddress record is selected to update the Customer table?

Answer 1

Something like this should do the trick.

UPDATE c
SET c.AddressLine1 = a.Line1
FROM Customer c
    JOIN
    (
        SELECT CustomerID, MAX(ID) AS LatestId
        FROM CustomerAddress
        GROUP BY CustomerID
    ) latest ON c.ID = latest.CustomerID
    JOIN CustomerAddress a ON latest.LatestId = a.ID