SQLite Forum

Shortcut to change a non changing update or upsert in a noop
Login
Well, I did some testing by updating some stuff that did not need updating and querying the change counts, with astonishing results - SQLite actually updates them.

```
  -- SQLite version 3.35.4  [ Release: 2021-04-02 ]  on  SQLitespeed version 2.1.3.11.
================================================================================================

CREATE TABLE t(a,b);

INSERT INTO t(a,b) VALUES
 (1, 'John')
,(2, 'Joan')
,(3, 'Jane')
,(4, 'Joanne')
,(5, 'Jenna')
,(6, 'Jack')
;

SELECT * FROM t;

  --       a     |b       
  -- ------------|--------
  --       1     |John    
  --       2     |Joan    
  --       3     |Jane    
  --       4     |Joanne  
  --       5     |Jenna   
  --       6     |Jack    


UPDATE t SET b='Jane' WHERE a=3;
SELECT Changes();

  --   Changes() 
  -- ------------
  --       1     


UPDATE t SET b='Jane' WHERE a=3;
SELECT Changes();

  --   Changes() 
  -- ------------
  --       1     


UPDATE t SET b='Jenna' WHERE a > 4;
SELECT Changes();

  --   Changes() 
  -- ------------
  --       2     

```
This same test in, for instance MySQL, yields 0 values where changes were not needed.

Possibilities I can think of:

  - 1 "Changes()" here reports the mathematical execution (honoured requests) rather than physical data writes, 
  - 2 or perhaps the SQLite devs figured that the incidence of UPDATEs that do NOT actually alter row contents are so low during typical operation that the overhead of first reading the data to "check-if-needed" is more expensive than just always writing,
  - 3 or maybe the trigger mechanisms need the actual writing to do their thing,
  - 4 or maybe it is because I use WAL journaling which always writes,
  - 5 or perhaps it is a genuine overlooked optimization opportunity (though I have some difficulty believing this hypothesis).

Whatever the reason, you may have good reason to actually avoid it, though the habit may cause slower execution (as per point 2 above).

I'm now quite curious.