public inbox for [email protected]  

help / color / mirror / Atom feed

Re: UPDATE-FROM and INNER-JOIN
2+ messages / 2 participants
[nested] [flat]

* Re: UPDATE-FROM and INNER-JOIN
@ 2024-08-05 15:01  Tom Lane <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Tom Lane @ 2024-08-05 15:01 UTC (permalink / raw)
  To: Dominique Devienne <[email protected]>; +Cc: [email protected]

Dominique Devienne <[email protected]> writes:
> 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.

But the whole point of that syntax is to be explicit about which
tables the ON clause(s) can draw from.  If we had a more complex
FROM clause, with say three or four JOINs involved, which part of
that would you argue the UPDATE target table should be implicitly
inserted into?  The only thing that would be non-ambiguous would
be to require the target table to be explicitly named in FROM
(and not treat that as a self-join, but as the sole scan of the
target table).  Some other RDBMSes do it like that, but it seems
like too much of a compatibility break for us.

Sadly, the SQL spec doesn't provide for a FROM clause in UPDATE,
so they offer no guidance.  But I doubt we are going to change
this unless the standard defines it and does so in a way that
doesn't match what we're doing.

			regards, tom lane






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: UPDATE-FROM and INNER-JOIN
@ 2024-08-05 15:09  Dominique Devienne <[email protected]>
  parent: Tom Lane <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Dominique Devienne @ 2024-08-05 15:09 UTC (permalink / raw)
  To: Tom Lane <[email protected]>; +Cc: [email protected]

On Mon, Aug 5, 2024 at 5:01 PM Tom Lane <[email protected]> wrote:
>
> Dominique Devienne <[email protected]> writes:
> > 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.
>
> But the whole point of that syntax is to be explicit about which
> tables the ON clause(s) can draw from.  If we had a more complex
> FROM clause, with say three or four JOINs involved, which part of
> that would you argue the UPDATE target table should be implicitly
> inserted into?

Wherever an update-target-column was referenced in an ON clause.
Like SQLite used to support. I.e. possibly multiple times even, I guess.
Yes that does imply the update-target table in not explicitly named
in the FROM clause, specifically in the UPDATE-FROM case.

Personally I don't find that "offensive", it's explicitly part of an UPDATE.

> The only thing that would be non-ambiguous would
> be to require the target table to be explicitly named in FROM
> (and not treat that as a self-join, but as the sole scan of the
> target table).  Some other RDBMSes do it like that, but it seems
> like too much of a compatibility break for us.

The (old for now) SQLite way would be lifting a restriction,
so that wouldn't be a backward incompatible change IMHO

> Sadly, the SQL spec doesn't provide for a FROM clause in UPDATE,
> so they offer no guidance.

And that's precisely why SQLite and PostgreSQL agreeing on a precedent
would be nice.

> But I doubt we are going to change
> this unless the standard defines it and does so in a way that
> doesn't match what we're doing.

OK. Fair enough. I'm just expressing a personal opinion above.
Which the SQLite Forum OP also supports I'd guess. FWIW.

Thanks for your input Tom. --DD






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-08-05 15:09 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-05 15:01 Re: UPDATE-FROM and INNER-JOIN Tom Lane <[email protected]>
2024-08-05 15:09 ` Dominique Devienne <[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