public inbox for [email protected]help / color / mirror / Atom feed
Re: UPDATE-FROM and INNER-JOIN 3+ messages / 3 participants [nested] [flat]
* Re: UPDATE-FROM and INNER-JOIN @ 2024-08-05 13:56 Tom Lane <[email protected]> 2024-08-05 14:36 ` Re: UPDATE-FROM and INNER-JOIN Dominique Devienne <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2024-08-05 13:56 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: [email protected] Dominique Devienne <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UPDATE-FROM and INNER-JOIN 2024-08-05 13:56 Re: UPDATE-FROM and INNER-JOIN Tom Lane <[email protected]> @ 2024-08-05 14:36 ` Dominique Devienne <[email protected]> 2024-08-05 14:57 ` Re: UPDATE-FROM and INNER-JOIN David G. Johnston <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Dominique Devienne @ 2024-08-05 14:36 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] On Mon, Aug 5, 2024 at 3:56 PM Tom Lane <[email protected]> wrote: > Dominique Devienne <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UPDATE-FROM and INNER-JOIN 2024-08-05 13:56 Re: UPDATE-FROM and INNER-JOIN Tom Lane <[email protected]> 2024-08-05 14:36 ` Re: UPDATE-FROM and INNER-JOIN Dominique Devienne <[email protected]> @ 2024-08-05 14:57 ` David G. Johnston <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: David G. Johnston @ 2024-08-05 14:57 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected] On Mon, Aug 5, 2024 at 7:36 AM Dominique Devienne <[email protected]> wrote: > I'd rather SQLite and PostgreSQL continue to agree on this, > 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-08-05 14:57 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-08-05 13:56 Re: UPDATE-FROM and INNER-JOIN Tom Lane <[email protected]> 2024-08-05 14:36 ` Dominique Devienne <[email protected]> 2024-08-05 14:57 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox