how to check update statement really modify a record
(1) By sqlite3_preupdate_count (XiongZaiBingGan) on 2020-07-14 12:34:33 [link]
hi,when i use sqlite3_exec() to do "update table set column_name=value where id=911;". but if no sql syntax,the sqlite3_exec() always return SQLITE_OK even if 'where' clause is not true (no record that id equal to 911). is there any method to check the update statement is really modify a record ?
(2) By Donald Griggs (dfgriggs) on 2020-07-14 13:31:06 in reply to 1 [link]
Hello, Yes. https://www.sqlite.org/lang_corefunc.html#changes
(3) By Richard Hipp (drh) on 2020-07-14 13:35:29 in reply to 1 [link]
> is there any method to check the update statement is really modify a record ? [sqlite3_changes()](https://www.sqlite.org/c3ref/changes.html)
(4) By Ryan Smith (cuz) on 2020-07-14 13:51:20 in reply to 1 [link]
> is there any method to check the update statement is really modify a record ? Do you mean if it "has modified" a record, or if it "will modify" a record? If you simply want to know if a record was modified or not, the page posted by others is perfect. If you want to know if it WILL modify a record, or if there was definitely a record to modify, you have to issue two statements, the first might be: ``` SELECT 1 FROM table WHERE id=911 AND column_name<>value; ``` If that returns any row, then it means your update statement WILL modify the DB. No rows means it won't. As far as understanding the case where: > (no record that id equal to 911) You really cannot get away from issuing: ``` SELECT 1 FROM table WHERE id=911; ``` Which is the only good way to know if id 911 exists in the table or not. Another way to check AFTER the fact if an update succeeded (if needed), is to formulate this: ``` UPDATE table SET column_name=value WHERE id=911; SELECT 1 FROM table WHERE id=911 AND column_name=value; ``` If the SELECT returns any rows, the UPDATE either succeeded or was not needed, but there definitely is a Row with that id and value now. Good luck, Ryan
(5) By doug (doug9forester) on 2020-07-14 16:01:29 in reply to 3 [link]
I assume a "change" using UPDATE ... means that the appropriate values were set to the values in the UPDATE statement, and not that the values were "changed". That is, if the current value of a field is 11 and your UPDATE sets it to 11 (the same value), a "change" will be recorded. There is nothing in the docs specifying that the value changed enters into the calculation.
(6) By sqlite3_preupdate_count (XiongZaiBingGan) on 2020-07-15 01:33:51 in reply to 2 [link]
thanks ,Your suggestion solved my problem. in myself idea,sqlite3_exec("update statement") return the modified row count may be better because it's no need invoke sqlite3_changes(). why the author don't implement sqlite3_exec return affected row count?
(7) By sqlite3_preupdate_count (XiongZaiBingGan) on 2020-07-15 01:34:18 in reply to 3 [link]
thanks ,Your suggestion solved my problem. in myself idea,sqlite3_exec("update statement") return the modified row count may be better, because it's no need invoke sqlite3_changes() to get affected row count. why the author don't implement sqlite3_exec return affected row count?
(8) By sqlite3_preupdate_count (XiongZaiBingGan) on 2020-07-15 01:50:10 in reply to 4 [link]
i just want to know if a record was modified or not,i get the answer by other post. in the situation: begin; 1:sqlite3_exec("insert statement..."); 2:sqlite3_exec("update table set xx=abc where id=911"); 3:sqlite3_exec("insert statement..."); commit; if step 1,2,3 all success,invoke commit,otherwise rollback. in step 2 ,because of 911 is a invalid value,so update statement can't find a record which id equal to 911 in table ,i think update fail.so step 3 in skipped. the transaction rollback.but sqlite3_exec still return sqlite_ok.so i want to check a record was modified or not. thank you all the same.:)
(9) By Simon Slavin (slavin) on 2020-07-15 12:46:09 in reply to 8 [link]
There are two non-error situations which might prevent <code>UPDATE</code> from making any changes: 1. a <code>WHERE</code> clause which is satisfied by no rows 2. the <code>WHERE</code> clause identifies rows, but the values in those rows are the same as the <code>SET</code> clause would change them to In both cases the <code>UPDATE</code> command is a complete success. SQLite did exactly what you told it to, and ran into no problems while doing it, so it returns SQLITE_OK. Examples of failure would be "no such table" or "database locked". If you want to know whether rows will be updated by <code>UPDATE</code>, do the equivalent <code>SELECT</code> first, and find out whether any rows are returned. Or use a function which counts changes, as recommended upthread.
(10) By doug (doug9forester) on 2020-07-15 14:46:12 in reply to 9 [link]
Simon, I'm an now confused. I assumed that my post earlier about updating a value with the same value would get some response. I assumed that the change count would get incremented if you "UPDATE" a field with the same value it had. You seem to be saying that an update will not happen if the value will not change. And therefore the change count will not increment. Is that true?
(11) By Stephan Beal (stephan) on 2020-07-15 14:53:26 in reply to 10 [link]
> You seem to be saying that an update will not happen if the value will not change. And therefore the change count will not increment. Is that true? It's trivial to test in the sqlite3 shell: ``` sqlite> create table t(a); insert into t(a) values(1),(2),(3); sqlite> update t set a=4 where a=1; -- new value sqlite> select changes(); 1 sqlite> update t set a=4 where a=4; -- same value as before sqlite> select changes(); 1 sqlite> update t set a=4 where a=8; -- no such record sqlite> select changes(); 0 ``` Demonstrably yes: the change count *is* incremented when a row matches an update, regardless of whether the updated record previously had the same value in the updated field(s).
(12) By Ryan Smith (cuz) on 2020-07-15 17:31:36 in reply to 11 [link]
I never use the function in question, only because my pedantry for the optimal ensures I'd near never try update values that do not need it, nor depend on such a function to inform me of the success/incidence of it, but if someone asked me to guess at what it did, I confess I would be with Simon on that... For a function labelled "Changes" to report a positive value where absolutely Zero changes happened, seems very counter-intuitive. It probably is more useful, as the use-case in this thread suggests, but still feels wrong. Either way, my knowledge-base on the matter just got updated - thanks.
(13) By Stephan Beal (stephan) on 2020-07-15 17:40:01 in reply to 12 [link]
In order to know/report whether or not any values were *actually* modified, sqlite would have to read all of the possibly-to-be-updated fields of the older records for every row of *every* update and compare them to the being-written values in the new records (remember that sqlite doesn't update records in-place). The performance hit would be non-trivial and would affect *every* update, even though only a small percentage of cases actually make use of the changes values.
(14) By David Raymond (dvdraymond) on 2020-07-15 17:47:33 in reply to 11 [link]
Also of note is that along with changes() registering the change if the before and after values are all the same, ON UPDATE triggers will also fire if everything is the same before and after.
(15) By Simon Slavin (slavin) on 2020-07-15 21:26:14 in reply to 10 [link]
I could not find documentation on this specific matter, either in the documentation for <code>UPDATE</code> or in the documentation for <code>CREATE TRIGGER</code>. And if it makes sense both ways, and it's not documented, it can change. Once again I recommend that if you want to know how many rows will be found by an <code>UPDATE</code> you execute the equivalent <code>SELECT</code> and count the rows returned (or use <code>COUNT(*)</code>).
(16.1) By Keith Medcalf (kmedcalf) on 2020-07-16 18:47:57 edited from 16.0 in reply to 15 [link]
You can of course bypass the whole issue by phrasing your UPDATE statement more carefully. For example, only choosing candidates for which you know a change needs to be made: UPDATE t SET x=5 WHERE id==47 AND x IS NOT 5; Will only attempt to change rows where a change is required and will not attempt to change rows where a change is not required. It says nothing, of course, about whether or not there is a row after the update which has id == 47 and x == 5.
(17) By Keith Medcalf (kmedcalf) on 2020-07-15 23:16:08 in reply to 13 [link]
SQLite has to read and parse the entire row anyway otherwise it would not be able to run triggers or re-write the row (update it). Also note that the sql function changes() (or the C API sqlite3_changes does not return the number of rows "changed" by the previous SQL command on a connection. It returns the number of rows "affected" by the previously SQL command on the connection. Mutatis mutandis the total_changes() and sqlite3_total_changes() functions. There is a significant difference between "changed" and "affected". Perhaps the documentation should be made clear that the return value is the number of candidate rows "affected", and has nothing whatsoever to do with anything being changed.
(18) By Stephan Beal (stephan) on 2020-07-15 23:58:08 in reply to 17 [link]
> SQLite has to read and parse the entire row anyway otherwise it would not be able to run triggers or re-write the row (update it). Of course it has to read the rows, but my point is that it in order to know whether a row was actually modified (as oppose to "affected"), it would have to *compare* N fields *unrelated to the WHERE clause*: ``` update t set a=1, b=2, ... 24 columns c..z omitted... where z=99; ``` Such an update would, in order for "changes" to know whether anything was *actually* modified, compare up to 25 columns (a..y) which are irrelevant for the WHERE clause (i.e. would not otherwise be compared). Granted, it would, on average, normally have to compare far fewer columns, but the fact remains that, computationally speaking, that comparison could, for arbitrarily large fields in arbitrary numbers of rows, be expensive and would necessarily apply to *every* update because sqlite cannot predict whether the client would eventually ask for the number of modified rows. Taking that into account, the behaviour of "changes" makes perfect sense (though the feature admittedly has an unfortunate name).
(19) By anonymous on 2020-07-16 02:10:31 in reply to 18 [link]
Actually, there is the simpler possibility to check if there is the actual change, although it might not be worth it. Data of each row is encoded using a documented format; it can compare that with the original data to see if it changed, just by length compare and memcmp. What I think would be better is to be able to substitute your own btree implementations (which includes the pager too), including shims; this way you can implement it by yourself easily enough if you require it.
(20) By Ryan Smith (cuz) on 2020-07-16 16:03:53 in reply to 18
Wait... so you are telling me that while SQLite already READs the entire row, it then does NOT check the fields individually to decide if it has to actually do a write or not? It just blindly rewrites the row regardless because checking takes effort? (more than write IO?) I find it hard to believe, and if it is true, surely there is a huge opportunity for optimization here. And if it does check - then the point is moot about the effort of evaluating all the fields, because it already happens. One of these two things must be false.
(21) By Stephan Beal (stephan) on 2020-07-16 16:18:16 in reply to 20 [link]
> One of these two things must be false. Both are true: 1) sqlite has to read whole records in order to copy all of the fields (typically only a subset of which are affected by the update). sqlite necessarily does not update records in-place. 2) sqlite compares fields as required by the WHERE clause, but has no reason whatsoever to decode/compare any fields not matched by the WHERE (noting that a memcmp of the undecoded data would (A) require loading the whole field, of arbitrary size (maybe hundreds of MB), into RAM and (B) would not take collation into account). That behavior is reflected in the "changes" results. > I find it hard to believe, and if it is true, surely there is a huge opportunity for optimization here. Regardless, the current behavior is very likely set in stone on compatibility grounds. Keep in mind that comparing an arbitrary number of fields of arbitrary sizes in an arbitrary number of rows is computationally arbitrarily expensive.
(22) By Ryan Smith (cuz) on 2020-07-16 17:00:04 in reply to 21 [link]
Ok, so just to be sure we are 100% on the same page: Your claim is that SQLite, while reading the whole record, only checks the fields which appear in the WHERE clause. This would mean that, given a table t(a,b) with row values (1,5),(2,6) and (3,7), the statement: UPDATE t SET b = 5 WHERE a = 1; only checks field a for comparing it to 1, and because that is true for one row, it would cause a physical WRITE operation, no matter the fact the value of b is already 5?
(23) By Stephan Beal (stephan) on 2020-07-16 17:07:38 in reply to 22 [link]
> Your claim is that SQLite, while reading the whole record, only checks the fields which appear in the WHERE clause. That's my claim and (without having looked at the code) that's the behaviour reflected by the "changes" demonstration shown upthread. > UPDATE t SET b = 5 WHERE a = 1; Keeping in mind that that update might have 100 fields which would need to be compared. sqlite has a compile-time limit on the number of columns, [defaulting to 2000][limits]. Though that may seem excessive, software-generated databases with 100+ columns are not unknown. [limits]: https://www.sqlite.org/limits.html > only checks field a for comparing it to 1, and because that is true for one row, it would cause a physical WRITE operation, no matter the fact the value of b is already 5? That's what the current behaviour unambiguously implies (keeping in mind that it might not just be 1 field to compare, but might be 47 of them, 30 of which may contain multi-MB blobs and/or data with custom collations).
(24) By Keith Medcalf (kmedcalf) on 2020-07-16 17:40:37 in reply to 23 [link]
>> only checks field a for comparing it to 1, and because that is true for one row, it would cause a physical WRITE operation, no matter the fact the value of b is already 5? > That's what the current behaviour unambiguously implies (keeping in mind that it might not just be 1 field to compare, but might be 47 of them, 30 of which may contain multi-MB blobs and/or data with custom collations). Implies. But not necessarily does. In some cases no I/O operation will be generated to write a page that is the same as what it was previously, and in some cases the useless I/O will not be performed. There are many places which may perform this sort of "useless I/O filtering" ranging from the application itself, all the way down to the physical storage media hardware.
(25) By doug (doug9forester) on 2020-07-16 17:48:49 in reply to 24 [link]
Stephen, Why do you mention "custom collations"? For "set x=..", collation doesn't come into play, does it? The test would be "old.x == new.x". Collation doesn't apply to equality comparisons, does it?
(26) By Stephan Beal (stephan) on 2020-07-16 18:06:36 in reply to 25 [link]
> Why do you mention "custom collations"? Because any check for equivalence, which would be required for the hypothetical "was it *really* modified?" check, must (it seems to me) honor column-level collation (if any). Without that, "a" and "A" would be different values even if the collation is "nocase". If that collation is not honored, some percentage of users would rightfully point out that "a" and "A" are, for the "nocase" collation, equivalent, and therefore (under this hypothetical feature) should not qualify as a change. Others would, also arguably rightfully, beg to differ because the binary values differ. That said, that's all based on deduction, not any familiarity at all with the code, and therefore may be completely wrong.
(27) By David Raymond (dvdraymond) on 2020-07-16 18:46:09 in reply to 15 [link]
In [Release History](https://www.sqlite.org/changes.html) 3.24.0 `13. UPDATE avoids unnecessary low-level disk writes when the contents of the database file do not actually change. For example, "UPDATE t1 SET x=25 WHERE y=?" generates no extra disk I/O if the value in column x is already 25. Similarly, when doing UPDATE on records that span multiple pages, only the subset of pages that actually change are written to disk. This is a low-level performance optimization only and does not affect the behavior of TRIGGERs or other higher level SQL structures`
(28) By Gunter Hick (gunter_hick) on 2020-07-21 14:14:27 in reply to 11 [link]
Looking at the generated bytecode suggests that SQLite implements the UPDATE statement as an INSERT INTO ... SELECT. Given a table T with three columns a,b,c UPDATE T SET a = 5 WHERE b = 7; is implemented as INSERT INTO T SELECT 5,b,c FROM T WHERE b=7; and the sqlite3_changes() function returns the cardinality of the SELECT part.