僅更新最低 ID

我們有兩個在社會保障號上連接的表，但為了這個範例，我將其更改為僅在名稱上連接。一張表保存每筆銷售，另一張表保存員工的年薪。我需要將這些數據編譯到一個表中，並且只用年薪從個人銷售表中更新一行**。**如何更新每個員工的最小 saleid？

以下是範例 DDL：

Declare @Helper Table (empname varchar(50), saleid int, sal float)

Declare @Helper1 Table (empname varchar(50), sal float)

Insert Into @Helper (empname, saleid) Values
('Number One', 818181), 
('Number One', 19213), 
('Number One', 919131),
('Number One', 131311313)
,('Number Two', 131313), 
('Number Two', 9823), 
('Number Two', 24)

Insert Into @Helper1 (empname, sal) Values
('Number One', 44000.00), ('Number Two', 55000.00)

這是我想要的輸出：

empname     saleid  sal
Number One  19213   44000
Number One  818181  
Number One  919131  
Number One  131311313   
Number Two  24      55000
Number Two  9823    
Number Two  131313  

你可以嘗試類似的東西：

update h
set sal = t.sal
from @Helper h
join (
   select h.empname, h.saleid, h1.sal, row_number() over(partition by  h.empname order by h.saleId ) as n
   from @Helper h
   join @Helper1 h1 on h.empname = h1.empname
   ) t on h.empname = t.empname and h.saleid = t.saleid
where n = 1

@Helper（假設in on empname+的主鍵saleid）

類似於@irimias 的答案，但沒有額外的連接：

with upd_helper as
 ( select h.sal, 
          new_sal = h1.sal, 
          rn = row_number() over(partition by h.empname order by h.saleId)
   from @Helper as h
     join @Helper1 as h1 
     on h.empname = h1.empname
 )
update upd_helper
set sal = new_sal
where rn = 1 ;

在rextester.com測試

引用自：https://dba.stackexchange.com/questions/157895