Re: Update table with max occurance from another table

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

"Dan Winslow" <d(dot)winslow(at)cox(dot)net> wrote in message
news:GjwC9(dot)72961$hb(dot)65088(at)news1(dot)central(dot)cox(dot)net(dot)(dot)(dot)
> 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
>
>