Update table with greatest value from itself and another table

Question

I have two tables A and B with some data and a date (e.g. date of last update). The id is only unique in table A. I want to update table A with the last date (=maximum) for each id.

Table A:

id date maximum
---------------
1 5 NULL
2 6 NULL

Table B:

id date
-------
1 3
1 7
2 20

Result A after Update:

id date maximum
---------------
1 5 7
2 6 20

What I got so far works, but it looks kind of weird:

UPDATE a,b 
SET a.maximum = (
 SELECT GREATEST(MAX(b.date),a.date) WHERE a.id=b.id GROUP BY b.id) 
WHERE a.id = b.id
;

The double WHERE a.id=b.id confuses me especially. Can this be improved somehow?

Answer 1

I would update with a join to a derived table. This would likely perform better than your subselect as well as be more intuitive to read (in my opinion, at least).

UPDATE a
INNER JOIN (
 SELECT
 id,
 MAX(`date`) AS max_date
 FROM b
 GROUP BY id
) AS max_dates
 ON a.id = max_dates.id
SET a.maximum = GREATEST(a.`date`, max_dates.max_date)