Re: Update table with max occurance from another table

Argh, good suggestion. That would make things significantly easier. Thanks.

"Dan Winslow" <d.winslow@cox.net> wrote in message
news:GjwC9.72961$hb.65088@news1.central.cox.net...
> Yes, I tried this, but it doesn't like the order or the limit clause in
> sub-selects.
>
> "Mike Beachy" <beachy@marketboy.com> wrote in message
> news:20021119191946.GA6703@marketdude.com...
> > 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
>
>