UPDATE table element using data and id from another table

Question

I need to update an element the row for id=N in table A with derived data from Table B.

Table A has uid as its primary key, and contains DATE element foo.

Table B has a_uid as an element, for each of which there are multiple rows for DATE element b_foo.

Conceptually, what I want to do is

UPDATE A SET foo=MAX(b_foo) WHERE uid = a_uid

Table A:

uid INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
foo DATE

Table B:

b_uid INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
a_uid INT(10) UNSIGNED,
b_foo DATE

I feel like this should be more obvious than I'm finding it to be; maybe it's because it's a COVID Monday? Regardless, many thanks for help!

Answer 1

What you want is called an UPDATE JOIN

First you must create a query that has the MAX(b_foo) for every a_uid:

SELECT a_uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid;

Making this a subquery, you can perform the UPDATE JOIN as follows:

UPDATE A INNER JOIN
(SELECT a_uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid) C
ON A.id = C.a_uid SET A.foo = C.max_b_foo;

or you can get fancy and do

UPDATE A INNER JOIN
(SELECT a_uid uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid) C
USING (uid) SET A.foo = C.max_b_foo;

GIVE IT A TRY !!!