query failed , don't know why

shouldn't that be m.payments and not m,payments?

Brian
----- Original Message -----
From: "Jeff MacDonald" <jeff@pgsql.com>
To: <pgsql-sql@postgresql.org>; <pgsql-hackers@postgresql.org>
Sent: Friday, June 23, 2000 2:53 PM
Subject: [HACKERS] query failed , don't know why

UPDATE members m,payments p SET m.status = 2 WHERE p.paydate >

'now'::datetime - '1 month'::timespan and p.productid = 'xxxxxxx' and m.gid
= p.gid

UPDATE members m,payments p SET m.status = 2
WHERE p.paydate > 'now'::datetime - '1 month'::timespan
and p.productid = 'xxxxxxx' and m.gid = p.gid

Try

UPDATE members set status = 2
FROM payments p
WHERE p.paydate > timestamp 'now' - interval '1 month'
AND p.productid = 'xxxxxxx' and members.gid = p.gid;

Jeff MacDonald <jeff@pgsql.com> writes:

UPDATE members m,payments p SET m.status = 2 WHERE p.paydate > 'now'::datetime - '1 month'::timespan and p.productid = 'xxxxxxx' and m.gid = p.gid
i'm trying to run that query and i'm getting
"parse error near m"
but it looks ok to me

OK according to what reference? SQL92 doesn't allow anything but a
simple <table name> between UPDATE and SET --- no aliases, much less
multiple table names.

regards, tom lane