CODEWITHSUNDEEP
sqlsql-serversql-update
Kaptah
Kaptah

Reputation: 9891

Update by the newest value in another table

I have salesperson´s in TableTap and i need to udpate these into TableNim per item. TableTap has a date tap_Date and i need to get the newest occurence of salesperson. Item is a unique value in TableNim. 

TableTap
| salesperson   | item  | tap_Date
|---------------|-------|-----------
| person1       | item1 | 2020-01-20
| person1       | item1 | 2020-01-21
| person1       | item2 | 2020-01-22
| person2       | item2 | 2020-01-22
| person2       | item3 | 2020-01-23

Result should be:

TableNim
| salesperson   | item  |
|---------------|-------|
| person1       | item1 |
| person2       | item2 |
| person2       | item3 |

I've tried the following

UPDATE TableNim SET TableNim.myyja =
(SELECT TOP 1 salesperson FROM TableTap
WHERE spec = 1 
and salespersons > '' 
and customer > ''
ORDER BY tap_Date DESC)
FROM TableNim, TableTap
WHERE
TableTap.item = TableNim.item

The result of the query ends up to have just one same salesperson in every row. Any help?

Upvotes: 0

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

A very simple method is a correlated subquery or cross apply:

update n
    set item = t.item
    from TableNim n cross apply
         (select top (1) t.*
          from TableTap t
          where t.salesperson = n.salesperson
          order by tap_date desc
         ) t;

With an index on TableTab(salesperson, tap_date desc, item), this often performs better than window functions.

Upvotes: 0

George Joseph
George Joseph

Reputation: 5922

You can try the following

update tablenim 
   set salesperson=b2.salesperson
  from (select row_number() over(partition by item order by tap_date desc) as rnk
              ,salesperson
              ,item 
          from tabletap
        ) b2
  join tablenim b 
    on b.item=b2.item
   and b2.rnk=1

Upvotes: 1

Related Questions