Can SQLite Perform UPSERT Operations Without a Preliminary SELECT Statement?

SQLite’s UPSERT operation: No need for pre-SELECT statement

Question:

According to Wikipedia’s definition of UPSERT, it combines the INSERT and UPDATE operations. The goal is to implement a single operation that updates an existing record or inserts a new record based on whether the record exists.

Question:

Is it possible to perform a UPSERT in SQLite without first doing a SELECT to determine if an update or insert is required?

Answer:

UPSERT support in SQLite

SQLite version 3.24.0 introduced support for UPSERT using the ON CONFLICT clause.

Grammar:

<code class="language-sql">INSERT OR REPLACE INTO 表名 (列1, 列2, ...)
VALUES (值1, 值2, ...)
ON CONFLICT (约束名) DO UPDATE SET 列1 = 值1, 列2 = 值2, ...</code>

Example:

Consider a table with columns id, name and role. Let's create a UPSERT statement that updates name and role if id exists, otherwise inserts a record with default value for role.

<code class="language-sql">INSERT OR REPLACE INTO Employee (id, name, role)
VALUES (1, 'John Doe', 'CEO')
ON CONFLICT (id) DO UPDATE SET name = 'John Doe', role = 'CEO';</code>

Alternative:

Before SQLite supported UPSERT, there were some cumbersome ways to achieve similar functionality:

• Partial INSERT: Insert only the columns you want to update, leaving other columns as default. However, this only works if the default value is acceptable.
• Conditional INSERT: Use a SELECT statement to check whether the record exists, and then execute the corresponding INSERT or UPDATE statement. This approach requires multiple database accesses, which may add overhead.

Comparison with SELECT:

UPSERT is usually more efficient than SELECT first because it performs the insert or update operation in one query. However, if the table is very large, SELECT may be faster in some cases.

The above is the detailed content of Can SQLite Perform UPSERT Operations Without a Preliminary SELECT Statement?. For more information, please follow other related articles on the PHP Chinese website!