public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dominique Devienne <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: UPDATE-FROM and INNER-JOIN
Date: Mon, 5 Aug 2024 16:36:33 +0200
Message-ID: <CAFCRh-8-DsfASidDtLPSkx10JMer_AuYOrXSa8HrqQgqEKvyNA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAFCRh-_Hm87rHcGNvScJ8AW9wdOekBnCP3yfGMWQdyoQYxHcZw@mail.gmail.com>
	<[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

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: UPDATE-FROM and INNER-JOIN
  In-Reply-To: <CAFCRh-8-DsfASidDtLPSkx10JMer_AuYOrXSa8HrqQgqEKvyNA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox