sql update max smartries

[Vic Cekvenich]

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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Bruno Wolff III]

On Thu, Oct 21, 2004 at 18:14:15 -0500,
Vic Cekvenich <ce***********@ 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.msg id
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;

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org