The developers have already discussed (internally) adding these features for 3.33, using the PostgreSQL syntax.

The way you do this now is:

       REPLACE INTO TableA SELECT ... FROM ...;

See for example the Fossil code at https://fossil-scm.org/fossil/artifact/414c5b8e0150?ln=983-990. This code seems like it would be easier to read and understand if it were written like this:

    UPDATE description
       SET summary=CASE WHEN plink.isprim THEN ''
                   ELSE 'merge '
                   END || 'parent of checkin',
           ref=blob.uuid
      FROM plink, blob
     WHERE description.summary='unknown'
       AND plink.cid=description.rid
       AND blob.rid=plink.pid;

I.e. the UPDATE ... from ... JOIN syntax allows to modify only some columns,
while the REPLACE INTO ... SELECT forces you to select and replace all columns?
That's the main benefit? Would be nice indeed.

Isn't REPLACE equivalent to a DELETE followed by an INSERT, potentially also
breaking cascading Foreign Keys as a side-effect, while UPDATE wouldn't?

Would be a doubly-nice to avoid that pitfall, assuming I'm right above.

Yes.

In theory an update with a FROM clause is a CURSORed update. That is:

UPDATE a
   SET ...
 FROM b[, ...]
WHERE ...

is equivalent to defining a cursor thusly:

SELECT *
  FROM a,b[, ...]
 WHERE ...
FOR UPDATE OF a

and then for each candidate executing

UPDATE a SET ... WHERE CURRENT OF CURSOR

Different RDBMS permit different definitions of the update and the cursor. In some implementations, one can update ANY table by simply using qualifications in the SET clause. Sometimes the target "a" is included in the cursor, and sometimes it merely specifies WHICH table in the cursor is the default for unqualified names in the SET clause. That is, sometimes:

UPDATE a
   SET bunny = 1
  FROM b
 WHERE a.this == b.that

is an invalid statement because "a" is not part of the cursor, and

UPDATE a
   SET bunny = 1
  FROM a, b
 WHERE a.this == b.that

is valid because the cursor is actually defined by the tables in the FROM clauses and you cannot update a table that does not appear there.

However, you can (currently) achieve the same effect by using the INSERT or REPLACE and getting rid of REPLACE and making it an INSERT ... ON CONFLICT UPDATE statement, processing every row as a CONFLICT. As in:

INSERT INTO a (rowid, bunny) SELECT a.rowid, 1 as bunny FROM a, b WHERE a.this==b.that ON CONFLICT (rowid) UPDATE SET bunny = excluded.bunny;

However, you can (currently) achieve the same effect by using the INSERT or REPLACE and getting rid of REPLACE and making it an INSERT ... ON CONFLICT UPDATE statement, processing every row as a CONFLICT. As in:

INSERT INTO a (rowid, bunny) SELECT a.rowid, 1 as bunny FROM a, b WHERE a.this==b.that ON CONFLICT (rowid) UPDATE SET bunny = excluded.bunny;

This is true, and is something I have done. However, the problem with this is that upsert is not supported for virtual tables.

Just to clarify: My original request is about simple UPDATE statements only, neither REPLACE INTO nor UPSERT.

UPDATE a SET bunny = 1 FROM a, b WHERE a.this == b.that

The syntax Keith mentioned, would be the perfect solution for my request :)

This is entirely different. Lets say table "a" has forty-seven trillion rows and table "b" has three rows. Assuming that there are indexes on a.this and b.that, then:

UPDATE a SET bunny=1 FROM a,b WHERE a.this==b.that;

would put b in the outer loop do 3 index seeks into a, and update three rows. Total time taken, nanoseconds.

UPDATE a SET bunny=1 WHERE EXISTS (SELECT 1 FROM b WHERE a.this = b.that);

would put a in the outer loop (forty-seven trillion rows) and for each of those do an index seek into b. There would be three "hits" and three rows updated. Total time taken: Your great-grandchildren might still be alive to see the query finish.

Presently the most efficient thing to do would probably be something like this:

WITH u(rowid) 
  as (
      select a.rowid 
        from a, b 
       where a.this == b.that
     )
UPDATE a 
   SET bunny=1 
 WHERE a.rowid IN (select rowid from u);

If you needed to compute the update, you would have to carry it in the CTE view and do correlated subqueries to get the update data, as in:

WITH u(rowid, bunny, rabbit) 
  as (
      select a.rowid, 1, b.rabbit
        from a, b 
       where a.this == b.that
     )
UPDATE a 
   SET (bunny, rabbit) = (select bunny, rabbit from u where u.rowid == a.rowid)
 WHERE a.rowid IN (select rowid from u);

Your wish has already been granted:

SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x(x);
sqlite> create table y(x,y);
sqlite> update x as a set x = y from y where y.x == a.x;
sqlite> update x as a set x = y from y as b where a.x == b.x;
sqlite>