# Update table with greatest value from itself and another table

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?

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)