From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dominique Devienne <ddevienne@gmail.com>
cc: pgsql-general@lists.postgresql.org
Subject: Re: UPDATE-FROM and INNER-JOIN
In-reply-to: <CAFCRh-8-DsfASidDtLPSkx10JMer_AuYOrXSa8HrqQgqEKvyNA@mail.gmail.com>
References: <CAFCRh-_Hm87rHcGNvScJ8AW9wdOekBnCP3yfGMWQdyoQYxHcZw@mail.gmail.com> <1064261.1722866217@sss.pgh.pa.us> <CAFCRh-8-DsfASidDtLPSkx10JMer_AuYOrXSa8HrqQgqEKvyNA@mail.gmail.com>
Comments: In-reply-to Dominique Devienne <ddevienne@gmail.com>
	message dated "Mon, 05 Aug 2024 16:36:33 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1073700.1722870110.1@sss.pgh.pa.us>
Date: Mon, 05 Aug 2024 11:01:50 -0400
Message-ID: <1073701.1722870110@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1073701.1722870110%40sss.pgh.pa.us>
Precedence: bulk

Dominique Devienne <ddevienne@gmail.com> writes:
> The reason I find the restriction damaging is that `FROM t1, t2 WHERE
> t1.c1 = t2.c2`
> is the "old" way to write joins, versus the "newer" `FROM t1 JOIN t2
> ON t1.c1 = t2.c2`
> which IMHO better separates "filtering" from "joining" columns. FWIW.

But the whole point of that syntax is to be explicit about which
tables the ON clause(s) can draw from.  If we had a more complex
FROM clause, with say three or four JOINs involved, which part of
that would you argue the UPDATE target table should be implicitly
inserted into?  The only thing that would be non-ambiguous would
be to require the target table to be explicitly named in FROM
(and not treat that as a self-join, but as the sole scan of the
target table).  Some other RDBMSes do it like that, but it seems
like too much of a compatibility break for us.

Sadly, the SQL spec doesn't provide for a FROM clause in UPDATE,
so they offer no guidance.  But I doubt we are going to change
this unless the standard defines it and does so in a way that
doesn't match what we're doing.

			regards, tom lane