Re: Update table with max occurance from another table

Ok, thank you, this seems to work. I do not undertand what the 'from b as c'
syntax though, much less why its necessary. At any rate, my thanks to you.

"Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote in message
news:20021119110924.D68336-100000@megazone23.bigpanda.com...
> On Tue, 19 Nov 2002, 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 :
>
> As a starting point, not using the postgresql extensions, or any thought
> to make it more efficient, maybe something like:
>
> update a set maxtype=(select type from b where b.id=a.id and
>  b.val=(select max(val) from b as c where c.id=b.id));
>
> I think using postgres extensions, you could do this as:
> update a set maxtype=b.type from (select distinct on (id) id, type
>  from b order by id, val desc) as b where a.id=b.id;
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org