sql - Update statement to set a column based the maximum row of another table -

i have family table:

select * family;  id | surname  | oldest | oldest_age     ---+----------+--------+-------  1 | byre     | null   | null  2 | summers  | null   | null  3 | white    | null   | null  4 | anders   | null   | null 

the family.oldest column not yet populated. there table of children:

select * children;  id | name     | age  | family_fk ---+----------+------+--------  1 | jake     | 8    | 1  2 | martin   | 7    | 2  3 | sarah    | 10   | 1  4 | tracy    | 12   | 3 

where many children (or no children) can associated 1 family. populate oldest column using update ... set ... statement sets name , oldest_age of oldest child in each family. finding name of each oldest child problem solved quite here: how can select rows max(column value), distinct column in sql?

however, don't know how use result of in update statement update column of associated table using h2 database.

the following ansi-sql syntax solves problem:

update family     set oldest = (select name                   children c                   c.family_fk = f.id                   order age desc                   fetch first 1 row                  ) 

in h2, think use limit 1 instead of fetch first 1 row only.

edit:

for 2 columns -- alas -- solution 2 subqueries:

update family     set oldest = (select name                   children c                   c.family_fk = f.id                   order age desc                   limit 1                  ),         oldest_age = (select age                       children c                       c.family_fk = f.id                       order age desc                       limit 1                      ); 

some databases (such sql server, postgres, , oracle) support lateral joins can this. also, row_number() can solve problem. unfortunately, h2 doesn't support functionality.