On Mon, Aug 5, 2024 at 5:01 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > > Dominique Devienne <ddevienne@xxxxxxxxx> 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
