Thread: 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
 

i'm trying to run that query and i'm getting 

"parse error near m"

but it looks ok to me 

i'm running postgresql 7.0.2 with freebsd 4.0 stable

jeff

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
>
> i'm trying to run that query and i'm getting
>
> "parse error near m"
>
> but it looks ok to me
>
> i'm running postgresql 7.0.2 with freebsd 4.0 stable
>
> jeff
>
>
>
>

> 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 = 2FROM payments pWHERE 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