Thread: sql update max smartries

I am trying to write a sigle command to update the max number from detail.

Something like:
update group set max_msgid=max(c.msgid)
    from group g, content c
    where g.id=c.g_id

So group is master, content is detail. I want group to stroe max(msgid)
from content.

Syntax help plz?
.V

On Thu, Oct 21, 2004 at 18:14:15 -0500,
  Vic Cekvenich <cekvenich.vic@portalvu.com> wrote:
> I am trying to write a sigle command to update the max number from detail.
>
> Something like:
> update group set max_msgid=max(c.msgid)
>     from group g, content c
>     where g.id=c.g_id
>
> So group is master, content is detail. I want group to stroe max(msgid)
> from content.

You don't want to put 'group' in the from clause, you will then be joining
'group' with itself (in addition to content).
Also you really don't want to join 'group' and 'content', but rather
'group' and (select c.g_id, max(c.msgid) from content group by g_id).
So the query should look something like (I didn't test this):
UPDATE "group" SET max_msgid=g.msgid
FROM (SELECT c.g_id, max(c.msgid) AS msgid FROM "content" c GROUP BY g_id) AS g
WHERE "group".id = g.g_id;