SQLite Forum

Feature request: extensions to UPDATE
Login
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);
```