On 2016/06/29 6:56 PM, Olivier Mascia wrote:
Dear all,
What's the expected behavior of statement "update or replace ..."?
(http://sqlite.org/lang_update.html)
create table T(K integer primary key, V text);
update or replace T set V='data' where K=1;
Clearly it does nothing.
Does this fit the intended behavior?
Would that mean 'or replace' in the context of an update statement is a void
operation?
...OR REPLACE here indicates a Conflict-resolution algorithm. Anything
after the OR in an UPDATE OR xxx statement relates to the conflict
resolution algorithm - i.e. What must be done if this change causes a
constraint to fail?
In all the next examples, assume two rows with Primary key IDs 1 and 2
already exist in the Database:
UPDATE OR FAIL SET ID = 1 WHERE ID = 2;
- indicates that the executions should stop immediately and produce a
Constraint-failed error without touching any transaction mechanics.
UPDATE OR ROLLBACK SET ID = 1 WHERE ID = 2;
- indicates that the executions should stop immediately and roll back
the entire active transaction.
UPDATE OR ABORT SET ID = 1 WHERE ID = 2;
- indicates that the executions should stop immediately and roll back
the current statement, but leave prior changes in the transaction as is.
UPDATE OR IGNORE SET ID = 1 WHERE ID = 2;
- indicates that the the update should be skipped. It's the "Do
nothing" check.
UPDATE OR REPLACE SET ID = 1 WHERE ID = 2;
- indicates that the Row with ID: 1 which already exists must be
deleted and then this row's ID should be set to 1. Dangerous!
Hope that clears it up,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
