On Mon, Aug 5, 2024 at 3:56 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Dominique Devienne <ddevienne@xxxxxxxxx> 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 PostgreSQL'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 = 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. Thanks, --DD [1]: https://www.sqlite.org/lang_update.html#update_from
