从另一个表更新表，为每个供应商选择最近的'日期'

I'm updating SupplierA with details from SupplierB.

我正在使用SupplierB的详细信息更新SupplierA。

SupplierB contains every order that suppliers have provided, however, when I insert into SupplierA, I want only one instance for each supplier, and therefore the most recent date associated with an order.

SupplierB包含供应商提供的每个订单，但是，当我插入SupplierA时，我只希望每个供应商有一个实例，因此最近的日期与订单相关联。

Currently I'm doing something like this;

目前我正在做这样的事情;

UPDATE SupplierA
SET LastSupplyDate = (SELECT [DATE] FROM SupplierB AS SB WHERE SB.Supplier COLLATE DATABASE_DEFAULT = SupplierA.SupNumber COLLATE DATABASE_DEFAULT)
WHERE [Site] = 2

Obviously this returns more than one value, how do I get the most recent one, is MAX() an option? I'd appreciate any ideas/thoughts?

显然这会返回多个值，如何获得最新值，MAX（）是一个选项吗？我很欣赏任何想法/想法？

3 个解决方案

#1

SET LastSupplyDate = (SELECT MAX([DATE])...

#2

UPDATE SupplierA
SET LastSupplyDate = (SELECT MAX([DATE]) FROM SupplierB AS SB WHERE SB.Supplier COLLATE DATABASE_DEFAULT = SupplierA.SupNumber COLLATE DATABASE_DEFAULT)
WHERE [Site] = 2

#3

UPDATE SupplierA SET LastSupplyDate =   
(  
  SELECT TOP 1 [DATE] FROM SupplierB AS SB  
  WHERE SB.Supplier
  COLLATE DATABASE_DEFAULT = SupplierA.SupNumber COLLATE DATABASE_DEFAULT 
  ORDER BY [DATE] DESC


) 
WHERE [Site] = 2