SQLite Forum

Feature request: extensions to UPDATE
Login

Feature request: extensions to UPDATE

(1) By tom (younique) on 2020-04-22 11:11:02 [link] [source]

The UPDATE statement lacks some useful features, such as:

- table alias ("UPDATE TableA AS a")
- joins ("UPDATE TableA INNER JOIN TableB ON ... SET a.rowX=1 WHERE b.rowY=64 AND a.rowZ=1")

Are there any plans for extending UPDATE in the near future?

(2) By Gunter Hick (gunter_hick) on 2020-04-22 11:49:00 in reply to 1 [link] [source]

Formulate the join as a subquery in the WHERE clause instead.

(3) By Richard Hipp (drh) on 2020-04-22 12:47:06 in reply to 1 [link] [source]

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;

(4) By ddevienne on 2020-04-22 13:05:04 in reply to 3 [source]

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.

(5) By Keith Medcalf (kmedcalf) on 2020-04-22 21:24:54 in reply to 4 [link] [source]

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;

(6) By anonymous on 2020-04-23 03:08:54 in reply to 5 [link] [source]

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.

(7) By tom (younique) on 2020-04-23 08:01:52 in reply to 5 [link] [source]

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 :)

(8) By Gunter Hick (gunter_hick) on 2020-04-23 10:01:44 in reply to 7 [link] [source]

UPDATE is already implemented as a SELECT of ROWID, unchanged fields and SET fields followed by an INSERT of the new row 

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

asql> explain query plan update a set bunny=1 where exists (select 1 from b where a.this = b.that);
id    parent         notu  deta
----  -------------  ----  ----
4     0              0     SCAN TABLE a
6     0              0     CORRELATED SCALAR SUBQUERY
10    6              0     SEARCH TABLE b USING COVERING INDEX b_that (that=?)

(9.2) By Keith Medcalf (kmedcalf) on 2020-04-23 12:04:20 edited from 9.1 in reply to 8 [link] [source]

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);

(10) By Steve M (steve_m) on 2022-04-20 18:29:27 in reply to 1 [link] [source]

I would also like to have the first feature (UPDATE TableA as a ...).

Postgres supports that syntax: https://www.postgresql.org/docs/current/sql-update.html

Thanks!

(11.1) By Keith Medcalf (kmedcalf) on 2022-04-20 20:16:08 edited from 11.0 in reply to 10 [link] [source]

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>