Thread: query failed , don't know why

query failed , don't know why

From
Jeff MacDonald
Date:
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





Re: query failed , don't know why

From
"Brian P. Mann"
Date:
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
>
>
>
>



Re: query failed , don't know why

From
Thomas Lockhart
Date:
> 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;
 


Re: query failed , don't know why

From
Tom Lane
Date:
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