Update Table1 Column with newest value from another table on same id

Question

I need to update Table1 value 'product' with newest column value from Table2, both have same ID. yyyymm -column states newst value.

TEMP_01_HALF

id04  |  product   |   col1  |  col2
-------------------------------------
10    |   null     |   data1 |  data2
10    |   null     |   datax |  datay
20    |   null     |   data  |  data
21    |   null     |   data3 |  data4

TEMP_02_FULL

id04  |  item     |   col1  |  yyyymm
-------------------------------------
10    |  duck     |   data1 |  201502
10    |  bear     |   datax |  201401
20    |  prod1    |   data  |  201501
21    |  prod2    |   data3 |  201402

Updated TEMP_01_HALF:

id04  |  product   |   col1  |  col2
-------------------------------------
10    |   bear     |   data1 |  data2   
10    |   bear     |   datax |  datay
20    |   prod1    |   data  |  data
21    |   prod2    |   data3 |  data4

I've tried few queries

Did not get this one to work:

UPDATE a
SET a.product = b.item
from TEMP_01_HALF a join
     (SELECT id04, item, MAX(yyyymm)
            FROM TEMP_02_FULL
          GROUP BY id04, item) b on a.id04 = b.id04

This one did not update with highest 'yyyymm' -value

UPDATE TEMP_01_HALF
SET    TEMP_01_HALF.product = TEMP_02_FULL.item
FROM   TEMP_01_HALF
JOIN   TEMP_02_FULL
ON     TEMP_01_HALF.id04 = TEMP_02_FULL.id04
JOIN   ( select id04
         ,      max(yyyymm) yyyymm
         from   TEMP_02_FULL
         group by id04
       )
       TEMP_02_FULL_MAX
on     TEMP_02_FULL.id04 = TEMP_02_FULL_MAX.id04

`duck` is `201502`... surely that's later than `bear` with `201401`? — Tanner, Sep 08 '15 at 12:50
possible duplicate of [UPDATE from SELECT using SQL Server](http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server) — Tanner, Sep 08 '15 at 12:51

score 1 · Answer 1 · answered Sep 08 '15 at 12:59

Try;

update a
set product = x.item
from TEMP_01_HALF a
join (
  select a.id04, a.item
  from TEMP_02_FULL a
  join (
    select id04, max(yyyymm) max_val
    from TEMP_02_FULL
    group by id04
  ) b
  on a.id04 = b.id04
  and a.yyyymm = b.max_val
) x 
on a.id04 = x.id04

score 1 · Answer 2 · answered Sep 08 '15 at 12:59

This is the way to JOIN to a single row of a second table, assuming id04 is a unique column:

UPDATE a
SET a.product = b.item
from TEMP_01_HALF a 
JOIN TEMP_02_FULL b
  ON b.id04 = (
  SELECT TOP 1 b1.id04
  FROM TEMP_02_FULL b1
  WHERE b1.id04=a.id04
  ORDER BY yyyymm DESC
)

score 1 · Accepted Answer · answered Sep 08 '15 at 13:08

1

You can use a correlated sub-query:

UPDATE TEMP_01_HALF 
SET product = (SELECT TOP 1 item
               FROM TEMP_02_FULL AS b
               WHERE a.id04 = b.id04
               ORDER BY yyyymm DESC)
FROM TEMP_01_HALF AS a

Demo here

answered Sep 08 '15 at 13:08

Giorgos Betsos

71,379
9
63
98

Update Table1 Column with newest value from another table on same id

3 Answers3