Subject: Re: UPDATE-FROM and INNER-JOIN

From: Tom Lane <[email-masked]>
Date: Mon, 05 Aug 2024 11:01:50 -0400

Dominique Devienne <[email-masked]> 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

---

From: Dominique Devienne <[email-masked]>
Date: Mon, 5 Aug 2024 17:09:20 +0200

On Mon, Aug 5, 2024 at 5:01 PM Tom Lane <[email-masked]> wrote:
> inserted into?

Wherever an update-target-column was referenced in an ON clause.
Like SQLite used to support. I.e. possibly multiple times even, I guess.
Yes that does imply the update-target table in not explicitly named
in the FROM clause, specifically in the UPDATE-FROM case.

Personally I don't find that "offensive", it's explicitly part of an UPDATE.

> The only thing that would be non-ambiguous would

The (old for now) SQLite way would be lifting a restriction,
so that wouldn't be a backward incompatible change IMHO

> so they offer no guidance.

And that's precisely why SQLite and PostgreSQL agreeing on a precedent
would be nice.

> But I doubt we are going to change

OK. Fair enough. I'm just expressing a personal opinion above.
Which the SQLite Forum OP also supports I'd guess. FWIW.

Thanks for your input Tom. --DD