1

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!

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 !!!

  • Bingo! Thanks very much, Rolando. – Dustin Boswell Jun 1 '20 at 14:50
  • If it solved your problem, you should mark it as an accepted answer – Lennart Jun 1 '20 at 19:10
  • thanks -- just did – Dustin Boswell Jun 2 '20 at 16:28

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.