On Tue, Nov 19, 2002 at 06:27:52PM +0000, Dan Winslow wrote:
> 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
How about:
update a set maxtype =
(select b.type from b where b.id = a.id order by b.val desc limit 1)
-mike