Thread: Updating table with max from another table

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

> 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

How about:

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

?

Regards,
Bjoern

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