Insert and update via Replace in SQLite

Source: Internet
Author: User
Tags bulk insert

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.