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  

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)

  • That's exactly what I was after, thank you, (And yes, safe assumption!) Dec 12 '16 at 13:37

Similar to @irimias' answer but without the extra join:

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 ;

Tested at rextester.com

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.