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)