0
\$\begingroup\$

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?

\$\endgroup\$
0
0
\$\begingroup\$

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)
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.