更新SQL Server表的数据从另一个表

Question

我有两个基本的SQL Server表：

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

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

CustomerAddress包含客户记录的多个地址。

对于每个客户记录，我想合并最近的CustomerAddress记录，其中最近的CustomerAddress记录由最高的CustomerAddress ID值确定。

我目前得到了以下内容：

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

其作品，但我怎么能保证，最近一段时间（最高ID）CustomerAddress记录选择更新Customer表？

Answer 1

像这样的东西应该做的伎俩。

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