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

Question

I have a Family table:

SELECT * FROM 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 is not yet populated. There is another table of Children:

SELECT * FROM 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 be associated with one family. I would like to populate the Oldest column using an UPDATE ... SET ... statement that sets it to the Name and Oldest_Age of the oldest child in each family. Finding the name of each oldest child is a problem that is solved quite well here: How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

However, I don't know how to use the result of this in an UPDATE statement to update the column of an associated table using the h2 database.

Gordon Linoff · Accepted Answer · 2015-04-25T15:40:29.490

The following is ANSI-SQL syntax that solves this problem:

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

In h2, I think you would use limit 1 instead of fetch first 1 row only.

EDIT:

For two columns -- alas -- the solution is two subqueries:

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

Some databases (such as SQL Server, Postgres, and Oracle) support lateral joins that can help with this. Also, row_number() can also help solve this problem. Unfortunately, H2 doesn't support this functionality.

Thanks, that looks surprisingly simple! Do you also know how I would go about updating two columns using the same subquery? I added an `Oldest_Age` column to my example that I totally forgot about. — MattLBeck, Apr 25 '15 at 15:36

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

1 Answers1