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 

```