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?
Advertisement
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 id
s 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