SQL As Understood By SQLite

[Top]

upsert

upsert-clause: hide

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL. UPSERT syntax was added to SQLite with version 3.24.0 (2018-06-04).

An UPSERT is an ordinary INSERT statement that is followed by the special ON CONFLICT clause shown above.

The syntax that occurs in between the "ON CONFLICT" and "DO" keywords is called the "conflict target". The conflict target specifies a specific uniqueness constraint that will trigger the upsert. The conflict target is required for DO UPDATE upserts, but is optional for DO NOTHING. A DO NOTHING upsert without a conflict target works the same as an INSERT OR IGNORE.

If the insert operation would cause the uniqueness constraint identified by the conflict-target clause to fail, then the insert is omitted and either the DO NOTHING or DO UPDATE operation is performed instead. In the case of a multi-row insert, this decision is made separately for each row of the insert.

Column names in the expressions of a DO UPDATE refer to the original unchanged value of the column, before the attempted INSERT. To use the value that would have been inserted had the constraint not failed, add the special "excluded." table qualifier to the column name.

Some examples will help illustrate the difference:

CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
INSERT INTO vocabulary(word) VALUES('jovial')
  ON CONFLICT(word) DO UPDATE SET count=count+1;

The upsert above inserts the new vocabulary word "jovial" if that word is not already in the dictionary, or if it is already in the dictionary, it increments the counter. The "count+1" expression could also be written as "vocabulary.count". PostgreSQL requires the second form, but SQLite accepts either.

CREATE TABLE phonebook(name TEXT PRIMARY KEY, phonenumber TEXT);
INSERT INTO phonebook(name,phonenumber) VALUES('Alice','704-555-1212')
  ON CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber;

In the second example, the expression in the DO UPDATE clause is of the form "excluded.phonenumber". The "excluded." prefix causes the "phonenumber" to refer to the value for phonenumber that would have been inserted had there been no conflict. Hence, the effect of the upsert is to insert a phonenumber of Alice if none exists, or to overwrite any prior phonenumber for Alice with the new one.

Note that the DO UPDATE clause acts only on the single row that experienced the constraint error during INSERT. It is not necessary to include a WHERE clause that restrictions the action to that one row. The only use for the WHERE clause at the end of the DO UPDATE is to optionally change the DO UPDATE into a no-op depending on the original and/or new values. For example:

CREATE TABLE phonebook2(
  name TEXT PRIMARY KEY,
  phonenumber TEXT,
  validDate DATE
);
INSERT INTO phonebook2(name,phonenumber,validDate)
  VALUES('Alice','704-555-1212','2018-05-08')
  ON CONFLICT(name) DO UPDATE SET
    phonenumber=excluded.phonenumber,
    validDate=excluded.validDate
  WHERE excluded.validDate>phonebook2.validDate;

In this last example, the phonebook2 entry is only updated if the validDate for the newly inserted value is newer than the entry already in the table. If the table already contains an entry with the same name and a current validDate, then the WHERE clause causes the DO UPDATE to become a no-op.

Limitations

UPSERT does not currently work for virtual tables.