SQLite changes () allows for unchanged UPDATE

Question

I have a question regarding the SQLite changes() function, which, according to the documentation , "returns the number of database rows that have been changed or inserted or deleted by the last completed INSERT, DELETE or UPDATE statement" (also see the documentation

Here changes() returns 1 as expected, because we just INSERT edited 1 line.

 UPDATE People SET Name = "Emma" WHERE Id = 1; SELECT changes(); 

Here changes() returns 1 , as expected, because 1 line was UPDATE d (i.e. actually changed: Name Person with Id = 1 was "Astrid" , and now "Emma" ).

 UPDATE People SET Name = "John" WHERE Id = 200; SELECT changes(); 

Here changes() returns 0 , as expected, because there is no line with Id = 200 .

So far so good. But now let's look at the next UPDATE , which really matches the existing line, but does not actually change it ( Name remains set to "Emma" ) ...

 UPDATE People SET Name = "Emma" WHERE Id = 1; SELECT changes(); 

Here changes() returns 1 , while of course I was hoping for 0 : - (.

Perhaps this would make sense if the function were called something like matched_rows() or affected_rows() . But for a function called changes() , and documented like this, this behavior seems illogical or confusing at best.

One way or another, can someone explain why this is happening, or, even better, propose an alternative strategy to achieve my goal in a reliable (and efficient) way?

All I can think of is to do something like SELECT * FROM People WHERE Id = x , compare all the returned values ​​of the column with the values ​​that I'm going to set in the UPDATE , and thereby decide whether I need to do UPDATE at all . But that cannot be very effective, can it? Of course, in this toy example this may not matter much, but in my actual application I am dealing with tables with many columns, some of which are (potentially large) BLOB s.

Answer 1

The database does not compare old and new values; any UPDATEd string is always considered "changed", even if the values ​​match. The documentation says that

UPDATE affects ... those rows for which the result of evaluating the WHERE clause as a logical expression is true.

If you want to check the old value, you must do this explicitly:

 UPDATE People SET Name = 'Emma' WHERE Id = 1 AND Name IS NOT 'Emma';