From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | josh(at)agliodbs(dot)com |
Cc: | sqllist <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: No JOINs in UPDATE ... FROM? |
Date: | 2001-04-26 22:15:15 |
Message-ID: | 25630.988323315@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-general pgsql-sql |
Josh Berkus <josh(at)agliodbs(dot)com> 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 statement: searched> ::=
UPDATE <target table>
SET <set clause list>
[ WHERE <search condition> ]
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-04-26 22:26:25 | Re: Can't start Pgsql 7.1 on FreeBSD 4.2 |
Previous Message | John Coers | 2001-04-26 22:11:56 | Performance with Large Volumes of Data |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-04-26 22:18:47 | Re: Problem connecting to postgres |
Previous Message | John Coers | 2001-04-26 22:11:56 | Performance with Large Volumes of Data |
From | Date | Subject | |
---|---|---|---|
Next Message | Doug McNaught | 2001-04-26 22:35:39 | Re: JDBC speed question. |
Previous Message | John Coers | 2001-04-26 22:11:56 | Performance with Large Volumes of Data |