Sql-server – Update Lowest ID Only

sql serversql-server-2008-r2t-sqlupdate

We have two tables that are joined on Social Security #'s, but for the sake of this example, I have altered it to join on name only. One table holds each individual sale, and the other table holds the employee's annual salary. I need to compile this data into one table, and only update ONE row from the individual sale table with the annual salary. How can I update the smallest saleid for each employee?

Below is sample 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)

This is my desired output:

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

Best Answer

you could try something like:

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

(assuming the primary key of @Helper in on empname + saleid)

Related Question