Re: UPDATE-FROM and INNER-JOIN

On Mon, Aug 5, 2024 at 5:01 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Dominique Devienne <ddevienne(at)gmail(dot)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?

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
> 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.

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

> Sadly, the SQL spec doesn't provide for a FROM clause in UPDATE,
> 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
> this unless the standard defines it and does so in a way that
> doesn't match what we're doing.

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