SQL:组合update和group-by？

Question

我有两张表：

events

mac | na
----------
abc | (null)
abc | (null)
def | (null)

enrichments

mac | na  | timestamp
---------------------
abc | na1 | 1
abc | na2 | 2
abc | na3 | 3
def | na4 | 1

现在我想用enrichments.na中的值更新events.na列，其中mac匹配，timestamp是该特定mac的最大timestamp。

对于此示例，结果将为：

abc | na3
abc | na3
def | na4

做这件事最有效的方法是什么？

谢谢!

Answer 1

UPDATE events AS e
SET na = 
    ( SELECT n.na
      FROM enrichments AS n
      WHERE n.mac = e.mac
      ORDER BY n.timestamp DESC
      FETCH FIRST 1 ROWS ONLY
    ) 

Answer 2

这应该适用于使用通用表表达式的SQL Server

create table events (mac nvarchar(3), na nvarchar(3))
insert into events values ('abc',null);
insert into events values ('abc',null);
insert into events values ('def',null);

create table enrichments (mac nvarchar(3), na nvarchar(3), timestamp int)
insert into enrichments values ('abc','na1',1)
insert into enrichments values ('abc','na2',2)
insert into enrichments values ('abc','na3',3)
insert into enrichments values ('def','na4',1)

with latestEnrichment (mac, latestTimestamp) as
    (select mac, MAX(timestamp)
    from    enrichments
    group by mac)
update  events
set     na = en.na
from    events e join enrichments en on e.mac = en.mac
        join latestEnrichment le on en.timestamp = le.latesttimestamp and
        en.mac = le.mac