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