Subject: Re: UPDATE-FROM and INNER-JOIN

From: Tom Lane <[email-masked]>
Date: Mon, 05 Aug 2024 09:56:57 -0400

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

---

From: Dominique Devienne <[email-masked]>
Date: Mon, 5 Aug 2024 16:36:33 +0200

> 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

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 5 Aug 2024 07:57:08 -0700

On Mon, Aug 5, 2024 at 7:36 AM Dominique Devienne <[email-masked]>
wrote:

> but not in a restrictive way.

I.e., you want to support the SQL Server syntax; allow the table named in
UPDATE to be repeated, without an alias, in which case it is taken to
represent the table being updated.  And then allow the usual FROM clause to
take form.

Personally I get around this by simply doing:

UPDATE tbl
FROM (...) AS to_update
WHERE tbl.id = to_update.id

A bit more verbose in the typical case but the subquery in FROM can be
separately executed during development then just plugged in.  There is no
noise in the outer where clause since its only purpose is to join the
subquery to the table to be updated.  The subquery has the full separation
of filters from joins that one would like to have.

David J.