public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Dominique Devienne <[email protected]>
Cc: [email protected]
Subject: Re: UPDATE-FROM and INNER-JOIN
Date: Mon, 05 Aug 2024 11:01:50 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFCRh-8-DsfASidDtLPSkx10JMer_AuYOrXSa8HrqQgqEKvyNA@mail.gmail.com>
References: <CAFCRh-_Hm87rHcGNvScJ8AW9wdOekBnCP3yfGMWQdyoQYxHcZw@mail.gmail.com>
	<[email protected]>
	<CAFCRh-8-DsfASidDtLPSkx10JMer_AuYOrXSa8HrqQgqEKvyNA@mail.gmail.com>

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

view thread (2+ 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: <[email protected]>

* 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