Updating a sql server table with data from another table
- by David G
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?
