Updating table with max from another table
От | Dan Winslow |
---|---|
Тема | Updating table with max from another table |
Дата | |
Msg-id | AjvC9.69703$hb.17906@news1.central.cox.net обсуждение исходный текст |
Список | pgsql-general |
Well, I *thought* I knew my way around SQL a little bit, but I have been beating my head on the following problem for a couple days now and I don't have any idea where to turn next. If you could suggest any strategies or places to look I would appreciate it very much. Thanks in advance. Given two table defs : create table a ( id integer, maxtype varchar(8) ); create table b ( id integer, type varchar(8), val integer ); and data rows as follows: select * from a; id | maxtype ----+--------- 1 | 2 | (2 rows) select * from b; id | type | val ----+-------+----- 1 | type1 | 5 1 | type2 | 6 2 | type1 | 19 2 | type2 | 4 (4 rows) And given the following task : update a from b such that a.maxtype is set equal to the b.type whose val number is the highest for that matching id, that is, the result : select * from a; id | maxtype ----+--------- 1 | type2 2 | type1 (2 rows) is to be obtained, how can this be accomplished with SQL statements? I am looking for a single (perhaps compound ) statement to do it, no procedural stuff
В списке pgsql-general по дате отправления: