SQLite Forum

Shortcut to change a non changing update or upsert in a noop
As far as I know and can see from the code and experimentation, UPDATE/INSERT which update a value to the same value as it already was is still an update to the row.

This is because it is what was commanded by the commander, you, who is sending the commands.  You can point at a parking lot full of cars and say "update the colour on those cars so they are all green".  If the parking lot contains 25 cars, then the correct answer for "how many cars are green" is 25.

The fact that 10 of them were *already* green and did not need to be re-painted is an "optimization" and has nothing to do with the fact that you commanded to make 25 cars green, and that is exactly what happened.

If you wanted to only paint the cars which were not already green with green paint and return the count of the cars so painted, that is what you should have commanded.

In similar fashion, SQLite3 will carry out your commands as directed.  However, the avoidance of painting a car that is already green with green paint is an optimization that occurs at a lower level (ie, in the parking lot analogy, it occurs when the painter goes to paint the car and sees that it is not in need of painting so does not bother to do so).  SQLite3 will similarly avoid extra I/O of unchanged pages.