Re: Updating table with max from another table

update a set maxtype = (select type from b where a.id = b.id order by
val desc limit 1);

Dan Winslow wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org