Re: Update table with max occurance from another table

Yes, I tried this, but it doesn't like the order or the limit clause in
sub-selects.

"Mike Beachy" <beachy(at)marketboy(dot)com> wrote in message
news:20021119191946(dot)GA6703(at)marketdude(dot)com(dot)(dot)(dot)
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster