Insert and update using Replace in Sqlite

You may want to insert a series of data in batches when processing a transaction. However, you do not want to re-Add the data after it is added once, I just want to update the original data. For example, I want to import a series of books to the database through Excel, and these books will be re-imported after your next edit, only modify the original data. The above is a business scenario.

In MSSQL, you can use:

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

Such SQL syntax representation. SQLite does not support such a syntax.

Correspondingly, Replace Into Or Insert Or Replace Into can be used in Sqlite.

 

Now, I use SQLite Developer's Sqlite client database management tool to create a data table. The corresponding fields are as follows:

 

Then, switch the tag to the "Index" column:

Here, I create an index by Name (Name) and Author (Author) and define it as a unique index. Save the data table.

This means that as long as the Name and Author correspond to the same, Replace into will be changed to Update. If the Replace is not completed, it will be converted to an Insert statement.

 

Therefore, I run the SQL statement in "query data:

Replace into tbl_book (Name, Author, PublishDate, pagecount, Memo) VALUES ('wf advanced programming design ', 'Bruce Bukovics', date (), 454, 'test ');

During the first execution, the command is converted to Insert because there is no data in the table;

During the second execution, because the table already contains data of the same "Name" and "Author", no insert is performed, and the command is converted to Update.

Therefore, when you execute the following statement:

Replace into tbl_book (Name, Author, PublishDate, pagecount, Memo) VALUES ('wf advanced programming design ', 'Bruce Bukovics', date (), 500, -- the total number of pages changed to 'test2' -- the remarks changed );

Execution result:

The page number and remarks have been changed. Update is executed here.

 

Then I modify the Name:

Replace into tbl_book (Name, Author, PublishDate, pagecount, Memo) VALUES ('wpf secrets ', -- Name changed to 'Bruce Bukovics', date (), 500, 'test2 ');

Execution result:

If you insert a book record, you can also try to change the Author record.

 

In this way, you can create a unique index in the table and use Replace to Insert OR Update.

 

The whole process is still very simple. This is my experience in Embedded projects :)

(Supplement: In. in. NET, there may be some problems when inserting or updating the date field. here you need to use ToString ("s") to insert or update the date format of the passed value)