The difference between replace, insert, and update in SQLite

This article goes from http://www.ithao123.cn/content-933827.html, where I thank the author

Android database operation, there are two ways to use Android to provide us with the database operation function Insert, UPDATE, replace, I use these three kinds. Another way is to use the database language to operate, that is, the use of execsql this function with SQLite database operation language to operate, SQLite SQL language and standard SQL language is similar, but if you want to have a targeted understanding, suggest you buy a book " SQLite authoritative guide, this book is the official SQLite website recommended, I bought a Chinese version, when on the sell.

Here are some of the most confusing points to be talked about:

First: Android gives us the Replace function, which is actually equivalent to an INSERT or update in the SQL language, roughly the meaning of this, meaning that if the only field you define in the table, such as _id=1, is the primary key! You take advantage of the parameters passed in Contentvalues, if the field that _id is 1 is present in the table, then it will not regenerate a record, but update the current row, of course, this is very different from the update, replace will completely erase all the information, Then regenerate a field based on the constraints you give, such as default values. Conversely, there is no _id field you have given, and a new _id=1 field is generated.

Second: Android Given the update function, if you use the update function, if you update_id=1 field, if the table does not exist _id=1 field, then will be an error. And you do the same with the SQLite statement, update the same field, if there is no _id=1 field, it will not perform any action, and will not error. That's the difference between them.

Third: The conflict resolution, this piece is more complicated, I also did not make very clear. You can define conflict resolution in the table, or you can define conflict resolution on the field. First of all, the function that you give with Android is not able to complete the conflict resolution, it is also possible that I have not found such function function? We typically use SQL statements, insert or ignore, to implement the conflict resolution of SQL statements. But the conflict resolution of SQL statements seems to be easy to solve without the field-level conflict resolution. Let's talk about the field-level conflict resolution, for example, if you want to implement a feature like this: you want to insert a record into a table, you want to insert a field that doesn't exist in the _id value, but you don't want to break any information that _id already exists, you can set a unique in the _id field, and set the conflict resolution to ignore on the only basis, so that you insert the data you want to insert, and keep the data that you don't want to destroy intact. Other conflict resolution, fail is when a constraint violation occurs, the SQLite command immediately terminates, Error! However, it is not recommended to use a non-recoverable constraint that violates records that have been modified before. Replace this is a good understanding, when a violation of the unique constraint, SQLite will cause this violation of the record deletion, that is, delete the original record, insert a new record, after debugging, replace does not save the original record of any information. Abort, when a constraint violation occurs, the SQLite Recovery command makes all changes and terminates the command. This is the default conflict resolution for SQLite and is the behavior defined by the SQL standard. The last one is rollback, and it's good to understand that when an action that violates a constraint occurs, the current command and the whole thing are terminated, and any operations and changes made by the current transaction and command are rolled back!

In this way, you can use the Insert Mate field-level ignore to complete the update operation for _id or other fields, compensate for Android update errors, and the update operation in the SQL statement does not add a flaw to the field that does not exist for _id.

The difference between replace, insert, and update in SQLite