public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Dominique Devienne <[email protected]>
Cc: [email protected]
Subject: Re: UPDATE-FROM and INNER-JOIN
Date: Mon, 05 Aug 2024 09:56:57 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFCRh-_Hm87rHcGNvScJ8AW9wdOekBnCP3yfGMWQdyoQYxHcZw@mail.gmail.com>
References: <CAFCRh-_Hm87rHcGNvScJ8AW9wdOekBnCP3yfGMWQdyoQYxHcZw@mail.gmail.com>

Dominique Devienne <[email protected]> writes:
> In https://sqlite.org/forum/forumpost/df23d80682
> Richard Hipp (Mr SQLite) shows an example of something
> that used to be supported by SQLite, but then wasn't, to be
> compatible with PostgreSQL.

For the archives' sake:

CREATE TABLE t1(aa INT,	bb INT);
CREATE TABLE t2(mm INT,	nn INT);
CREATE TABLE t3(xx INT,	yy INT);
UPDATE t1 SET bb = mm+xx FROM t2 INNER JOIN t3 ON nn=xx AND mm=aa;

yields

ERROR:  column "aa" does not exist
LINE 1: ... t1 SET bb = mm+xx FROM t2 INNER JOIN t3 ON nn=xx AND mm=aa;
                                                                    ^
DETAIL:  There is a column named "aa" in table "t1", but it cannot be referenced from this part of the query.


> Thus I'm curious as to why PostgreSQL refuses the first formulation.
> Could anyone provide any insights? Thanks, --DD

This seems correct to me.  The scope of the ON clause is just
the relations within the INNER JOIN, which does not include t1.
You would get the same from

SELECT * FROM t1, t2 INNER JOIN t3 ON nn=xx AND mm=aa;
ERROR:  column "aa" does not exist
LINE 1: SELECT * FROM t1, t2 INNER JOIN t3 ON nn=xx AND mm=aa;
                                                           ^

because again t1 is not part of the JOIN sub-clause.  (MySQL used
to get this wrong, many years ago, and it seems that has taught
a lot of people some strange ideas about syntactic precedence
within FROM clauses.  Postgres' behavior agrees with the SQL
spec here.)

			regards, tom lane

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: UPDATE-FROM and INNER-JOIN
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox