public inbox for [email protected]  

help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: Dominique Devienne <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: UPDATE-FROM and INNER-JOIN
Date: Mon, 5 Aug 2024 07:57:08 -0700
Message-ID: <CAKFQuwZ3jzHUVMJ533OhgmrO8hYkOugRAHJN8avZtOLkQxsfTg@mail.gmail.com> (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>

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.

view thread (3+ messages)

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], [email protected]
  Subject: Re: UPDATE-FROM and INNER-JOIN
  In-Reply-To: <CAKFQuwZ3jzHUVMJ533OhgmrO8hYkOugRAHJN8avZtOLkQxsfTg@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