Insert and update via Replace in SQLite

You may want to bulk insert a series of data when you bulk process a transaction, but when the data is added once, you do not want to add it again, just want to update the original data, for example: I want to import a series of books into the database through Excel, And these books after your next edit, re-import, just to modify the original data. The above is a business scenario.

In MSSQL, you can use things like:

?

IF NOTEXISTS(SELECT * FROM Book WHERE ….) THEN INSERT INTO ... ELSE UPDATE SET...

This is represented by SQL syntax. In SQLite, this syntax is not supported.

Instead, in SQLite, you can use Replace into or the Insert or Replace into syntax format.

Now, I use SQLite Developer's SQLite Client database management tool to create a data table, corresponding to the following fields:

Then, the label switches to the index bar:

Here I create an index of name (title) and author (author), and specify a unique index. Save the data table.

This means that as long as the name and author correspond to the same, the Replace into corresponds to Update, and if the same is not done, the corresponding Insert statement.

So I'm in query data, execute the SQL statement:

?

12345678910111213141516

REPLACE INTO tbl_book   (     Name ,     Author ,     PublishDate ,     pagecount ,     Memo   ) VALUES  (     ‘WF高级程序设计‘ ,     ‘Bruce Bukovics‘ ,     date( ) ,     454 ,     ‘Test‘  ) ;

At the first execution, the command is converted to insert because there is no data in the table;

When the second execution occurs, because the same "Name" and "Author" data already exist in the table, the insert is not inserted and the command is converted to update.

Therefore, when you execute the following statement:

?

12345678910111213141516

REPLACE INTO tbl_book   (     Name ,     Author ,     PublishDate ,     pagecount ,     Memo   ) VALUES  (     ‘WF高级程序设计‘ ,     ‘Bruce Bukovics‘ ,     date( ) ,     500 ,   -- 页码总数改变     ‘Test2‘ -- 备注改变  ) ;

Execution Result:

The page numbers and notes have changed, indicating that the update was performed here.

Then I change the name:

?

12345678910111213141516

REPLACE INTO tbl_book   (     Name ,     Author ,     PublishDate ,     pagecount ,     Memo   ) VALUES  (     ‘WPF揭秘‘ ,     -- 书名改变    ‘Bruce Bukovics‘ ,     date( ) ,     500 ,        ‘Test2‘  ) ;

Execution Result:

Insert a record of a book, you can also try to change the author, also insert records.

This allows you to create a unique index in the table and use replace to achieve the purpose of insert OR update.

Overall is very simple, this is what I do in the embedded project, a little experience:)

(add: In.) NET, there are some problems when inserting or updating date fields, where you need to insert or update the date format of the value passed by ToString ("s")

Insert and update via Replace in SQLite