Re: Update table with max occurance from another table

add 'as foo' to the end of the nested select...

update a set maxtype = (select bla bla bla desc limit 1 as foo)

Dan Winslow wrote:

>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
>>
>>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>