UPDATE table element using data and id from another table 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!
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 !!!