MIME-Version: 1.0
References: <CAFCRh-_Hm87rHcGNvScJ8AW9wdOekBnCP3yfGMWQdyoQYxHcZw@mail.gmail.com>
 <1064261.1722866217@sss.pgh.pa.us>
In-Reply-To: <1064261.1722866217@sss.pgh.pa.us>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Mon, 5 Aug 2024 16:36:33 +0200
Message-ID: <CAFCRh-8-DsfASidDtLPSkx10JMer_AuYOrXSa8HrqQgqEKvyNA@mail.gmail.com>
Subject: Re: UPDATE-FROM and INNER-JOIN
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-8-DsfASidDtLPSkx10JMer_AuYOrXSa8HrqQgqEKvyNA%40mail.gmail.com>
Precedence: bulk

On Mon, Aug 5, 2024 at 3:56=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dominique Devienne <ddevienne@gmail.com> 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.

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

The [SQLite doc][1] does mention:

1) "With UPDATE-FROM you can join the target table against other tables"
2) "The target table is not included in the FROM clause, unless the
intent is to do a self-join"

which one can easily read as the update-target-table being implicitly
part of the join,
and thus OK to JOIN-ON against. Yes it is the SQLite doc, and not PostgreSQ=
L's,
but naively I tend to agree with the OP (on the SQLite Forum) that it
"ought" to work.
In both SQLite (as it used to), and in PostgreSQL.

I'd rather SQLite and PostgreSQL continue to agree on this,
but not in a restrictive way. Which would imply PostgreSQL accepting
it (a tall order...).
Or perhaps SQLite should allow it back. And PostgreSQL catch up eventually?

The reason I find the restriction damaging is that `FROM t1, t2 WHERE
t1.c1 =3D t2.c2`
is the "old" way to write joins, versus the "newer" `FROM t1 JOIN t2
ON t1.c1 =3D t2.c2`
which IMHO better separates "filtering" from "joining" columns. FWIW.

Thanks, --DD

[1]: https://www.sqlite.org/lang_update.html#update_from