On Tue, Feb 7, 2012 at 11:42 AM, Scholz Maik (CM-AI/PJ-CF42) <
maik.sch...@de.bosch.com> wrote:
> Hi,
>
> I have some problem with my understanding of "UPDATE OR IGNORE" and the
> sqlite3_changes() function.
> My expectation is, that changes() gives the number of changed rows.
> When I use "UPDATE OR IGNORE" with same data, so no data change is needed,
> I expect that
> the number of changes is null.
>
> I am wrong?
>
UPDATE OR IGNORE means that the change is not applied if it would have
resulted in a uniqueness or check constraint violation. The change still
occurs if it is a "no-op" change - if the value being changed too is the
value that was in the table originally. If you want to avoid a no-op
change, use a WHERE clause:
UPDATE OR IGNORE mytable SET idint=1 WHERE idint<>1;
>
> How can I count the number of rows with data changed?
>
> Regards
>
> Maik Scholz
>
> My Test:
> .headers ON
> .echo ON
> CREATE TABLE mytable (version INT, idint INT, idblob BLOB);
>
> INSERT OR IGNORE INTO mytable (version,idint,idblob) VALUES (1,1,x'1111');
> SELECT total_changes(),changes();
>
> SELECT rowid,idint,hex(idblob) from mytable;
> SELECT total_changes(),changes();
>
> UPDATE OR IGNORE mytable SET idint=1;
> SELECT total_changes(),changes();
>
> SELECT rowid,idint,hex(idblob) from mytable;
>
> Execution Log:
> CREATE TABLE mytable (version INT, idint INT, idblob BLOB);
> INSERT OR IGNORE INTO mytable (version,idint,idblob) VALUES (1,1,x'1111');
> SELECT total_changes(),changes();
> total_changes()|changes()
> 1|1
> SELECT rowid,idint,hex(idblob) from mytable;
> rowid|idint|hex(idblob)
> 1|1|1111
> SELECT total_changes(),changes();
> total_changes()|changes()
> 1|1
> UPDATE OR IGNORE mytable SET idint=1;
> SELECT total_changes(),changes();
> total_changes()|changes()
> 2|1
>
> ??? Here I expect total_changes()==1 && changes()==0 because the idint
> column was not changed.
>
> SELECT rowid,idint,hex(idblob) from mytable;
> rowid|idint|hex(idblob)
> 1|1|1111
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
