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. For the archives' sake: CREATE TABLE t1(aa INT, bb INT); CREATE TABLE t2(mm INT, nn INT); CREATE TABLE t3(xx INT, yy INT); UPDATE t1 SET bb = mm+xx FROM t2 INNER JOIN t3 ON nn=xx AND mm=aa; yields ERROR: column "aa" does not exist LINE 1: ... t1 SET bb = mm+xx FROM t2 INNER JOIN t3 ON nn=xx AND mm=aa; ^ DETAIL: There is a column named "aa" in table "t1", but it cannot be referenced from this part of the query. > Thus I'm curious as to why PostgreSQL refuses the first formulation. > Could anyone provide any insights? Thanks, --DD This seems correct to me. The scope of the ON clause is just the relations within the INNER JOIN, which does not include t1. You would get the same from SELECT * FROM t1, t2 INNER JOIN t3 ON nn=xx AND mm=aa; ERROR: column "aa" does not exist LINE 1: SELECT * FROM t1, t2 INNER JOIN t3 ON nn=xx AND mm=aa; ^ because again t1 is not part of the JOIN sub-clause. (MySQL used to get this wrong, many years ago, and it seems that has taught a lot of people some strange ideas about syntactic precedence within FROM clauses. Postgres' behavior agrees with the SQL spec here.) regards, tom lane
