Received: from sss.pgh.pa.us (sss.pgh.pa.us [216.151.103.158]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3QMFK370446 for ; Thu, 26 Apr 2001 18:15:21 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f3QMFFR25633; Thu, 26 Apr 2001 18:15:15 -0400 (EDT) To: josh@agliodbs.com cc: sqllist Subject: Re: No JOINs in UPDATE ... FROM? In-reply-to: <3AE85E1E.2D39D02D@agliodbs.com> References: <3AE85E1E.2D39D02D@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Thu, 26 Apr 2001 10:42:54 -0700" Date: Thu, 26 Apr 2001 18:15:15 -0400 Message-ID: <25630.988323315@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200104/419 X-Sequence-Number: 2781 Josh Berkus writes: > UPDATE assignments SET status = -1 > FROM assignments JOIN orders ON assignments.order_usq = orders.usq > WHERE orders.status = -1; > However, I get an error of "Relation assignments referenced twice in > query." > Now, I can (and have) re-phrase the query so that PostgreSQL will > accept it. However, I was under the impression that the above was > standard SQL92. Am I mistaken? You are mistaken. SQL92 and SQL99 don't allow a FROM clause in UPDATE at all: they say it's just ::= UPDATE SET [ WHERE ] Postgres allows the clause, but treats it as supplying *additional* table references besides the target table reference. Thus the error. In other words: you can JOIN, but not against the target table. regards, tom lane