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




Fri, 23 Jun 2000 15:53:45 -0300 (ADT)
Jeff MacDonald <jeff@pgsql.com>
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
>
> 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
>
>
>
>


Fri, 23 Jun 2000 18:04:31 -0400
"Brian P. Mann" <bpmann@modempool.com>
Re: 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

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;

Sat, 24 Jun 2000 03:31:20 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: query failed , don't know why
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

Sat, 24 Jun 2000 00:37:09 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] query failed , don't know why 