MYSQL Update Column with higher values from other table

I’m having two tables in an mysql database. TABLE_A

|    id   |  val  |
|----------------|
|    a    |   1   |
|    b    |   5   |
|    c    |   5   |
|    d    |   4   |
|    e    |   5   |
|    f    |   7   |
|----------------|


 TEMPORARY TABLE_B
|----------------|
|    id   |  val  |
|----------------|
|    a    |   4   |
|    b    |   4   |
|    c    |   8   |
|    d    |   8   |
|    e    |   5   |
|    f    |   2   |
|----------------|

 TABLE_A after update
|----------------|
|    id   |  val  |
|----------------|
|    a    |   4   |
|    b    |   5   |
|    c    |   8   |
|    d    |   8   |
|    e    |   5   |
|    f    |   7   |
|----------------|

I want to update Table_A with values from Table_B, but only if they are higher. Has anyone a snippet for me?

Answer

One option uses the update/join syntax:

update table_a a
inner join (select id, max(val) val from table_b group by id) b
    on b.id = a.id and b.val > a.val
set a.val = b.val

If there are no duplicate ids in table_b, no need for aggregation:

update table_a a
inner join table_b b
    on b.id = a.id and b.val > a.val
set a.val = b.val