MIME-Version: 1.0
In-Reply-To: <CAFCRh-_Hm87rHcGNvScJ8AW9wdOekBnCP3yfGMWQdyoQYxHcZw@mail.gmail.com>
References: <CAFCRh-_Hm87rHcGNvScJ8AW9wdOekBnCP3yfGMWQdyoQYxHcZw@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Mon, 5 Aug 2024 06:04:48 -0700
Message-ID: <CAKFQuwYcFGgS9itAqsUj5Y02UtDXr8yBtac9w+v+45pLEu4U3Q@mail.gmail.com>
Subject: Re: UPDATE-FROM and INNER-JOIN
To: Dominique Devienne <ddevienne@gmail.com>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000006c0da0061eef52c1"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwYcFGgS9itAqsUj5Y02UtDXr8yBtac9w%2Bv%2B45pLEu4U3Q%40mail.gmail.com>
Precedence: bulk

--0000000000006c0da0061eef52c1
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Monday, August 5, 2024, Dominique Devienne <ddevienne@gmail.com> wrote:

> 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.
>
> Thus I'm curious as to why PostgreSQL refuses the first formulation.
> Could anyone provide any insights? Thanks, --DD
>

Interesting=E2=80=A6but not too surprising.  The joining condition between =
the
update relation and the from relation needs to be done in the where
clause.  You cannot reference columns of the update relation in the from
clause because the update relation is not named in the from clause.

There is still an underlying =E2=80=9Cwhy=E2=80=9D here that I don=E2=80=99=
t know=E2=80=A6

David J.

--0000000000006c0da0061eef52c1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Monday, August 5, 2024, Dominique Devienne &lt;<a href=3D"mailto:ddevien=
ne@gmail.com">ddevienne@gmail.com</a>&gt; wrote:<br><blockquote class=3D"gm=
ail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-le=
ft:1ex">In <a href=3D"https://sqlite.org/forum/forumpost/df23d80682" target=
=3D"_blank">https://sqlite.org/forum/<wbr>forumpost/df23d80682</a><br>
Richard Hipp (Mr SQLite) shows an example of something<br>
that used to be supported by SQLite, but then wasn&#39;t, to be<br>
compatible with PostgreSQL.<br>
<br>
Thus I&#39;m curious as to why PostgreSQL refuses the first formulation.<br=
>
Could anyone provide any insights? Thanks, --DD<br>
</blockquote><div><br></div><div>Interesting=E2=80=A6but not too surprising=
.=C2=A0 The joining condition between the update relation and the from rela=
tion needs to be done in the where clause.=C2=A0 You cannot reference colum=
ns of the update relation in the from clause because the update relation is=
 not named in the from clause.</div><div><br></div><div>There is still an u=
nderlying =E2=80=9Cwhy=E2=80=9D here that I don=E2=80=99t know=E2=80=A6</di=
v><div><br></div><div>David J.</div><div><br></div>

--0000000000006c0da0061eef52c1--