Re: UPDATE-FROM and INNER-JOIN

On Mon, Aug 5, 2024 at 7:36 AM Dominique Devienne <ddevienne(at)gmail(dot)com>
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.