Sqlite ON CONFLICT difference between ABORT and FAIL

Question

From http://www.sqlite.org/lang_conflict.html

ABORT When a corresponding violation of constraints occurs, the ABORT resolution algorithm interrupts the current SQL statement with a SQLITE_CONSTRAIT error and returns any changes made by the current SQL operation; but the changes caused by previous SQL operations in one transaction are saved and the transaction remains active. This default behavior and behavior have banned the SQL standard.

FAILURE When a corresponding violation of constraints occurs, the FAIL resolution algorithm aborts the current SQL statement with a SQLITE_CONSTRAINT error. But FAIL permission does not undo previous changes to the SQL query that failed, and does not complete the transaction. For example, if the UPDATE statement has detected a restriction violation in the 100th row that it is trying to update, then the first 99 line changes are saved, but changes in lines 100 and above never occur.

Both save the changes made before the statement that caused the violation of the restrictions, and do not complete the transaction. Therefore, I believe that the only difference is that the FAIL permission does not allow further changes, while ABORT only backs up only the conflicting statement. I understood?

Answer 1

The answer is simple: FAIL does not undo changes made by the current statement.

Consider two tables:

CREATE TABLE IF NOT EXISTS constFAIL (num UNIQUE ON CONFLICT FAIL); CREATE TABLE IF NOT EXISTS constABORT (num UNIQUE ON CONFLICT ABORT); INSERT INTO constFAIL VALUES (1),(3),(4),(5),(6),(7),(8),(9),(10); INSERT INTO constABORT VALUES (1),(3),(4),(5),(6),(7),(8),(9),(10); 

Statement

 UPDATE constABORT SET num=num+1 WHERE num<10 

won't work and won't change anything. But this offer

 UPDATE constFAIL SET num=num+1 WHERE num<10 

will update the first line, then it will work and leave 1 line updated, so the new values ​​will be 2, 3, 4, 5, 6, 7, 8, 9, 10