1.语法

附加条款： 隐藏

2.说明

UPSERT是添加到INSERT的子句，如果INSERT违反唯一性约束，则该子句将使INSERT表现为UPDATE或no-op。UPSERT不是标准的SQL。SQLite中的UPSERT遵循PostgreSQL建立的语法，具有概括性。

UPSERT是普通的INSERT语句，后跟一个或多个ON CONFLICT子句，如上面的语法图中所示。

“ ON CONFLICT”和“ DO”关键字之间的语法称为“冲突目标”。冲突目标指定将触发更新的唯一性约束。INSERT语句中的最后一个ON CONFLICT子句可以省略冲突目标，但所有其他ON CONFLICT子句都需要冲突目标。

如果插入操作将导致冲突目标唯一性约束失败，那么将省略插入操作，而是执行相应的DO NOTHING或DO UPDATE操作。按指定的顺序检查ON CONFLICT子句。如果最后一个ON CONFLICT子句忽略了冲突目标，那么如果任何唯一性约束失败（如果先前的ON CONFLICT子句未捕获），它将触发。

对于INSERT的每一行，只能运行一个ON CONFLICT子句，特别是第一个具有匹配冲突目标的ON CONFLICT子句。当ON CONFLICT子句触发时，该行的所有后续ON CONFLICT子句都会被绕过。

如果是多行刀片，则对刀片的每一行分别做出upsert决定。

UPSERT处理仅在唯一性约束下发生。“唯一性约束”是CREATE TABLE语句中的显式UNIQUE或PRIMARY KEY约束，或者是唯一索引。UPSERT不会针对失败的NOT NULL，CHECK或外键约束或使用触发器实现的约束进行干预。

在尝试执行INSERT之前，DO UPDATE表达式中的列名称是指该列的原始不变值。要使用在约束未失败的情况下将要插入的值，请添加特殊的“已排除”。表限定符到列名。

2.1。例子

一些示例将帮助说明UPSERT的工作方式：

创建表词汇（单词TEXT PRIMARY KEY，计数为INT DEFAULT 1）；
插入词汇表（单词）值（“快乐”）
  ON CONFLICT（word）进行更新设置count = count + 1;

如果该单词尚未在字典中，或者如果它已经在字典中，则上面的upsert会插入新词汇单词“ jovial”，从而使计数器增加。“ count + 1”表达式也可以写为“ vocabulary.count”。PostgreSQL需要第二种形式，但是SQLite接受任何一种。

创建表电话簿（名称TEXT PRIMARY KEY，电话号码TEXT）；
插入电话簿（名称，电话号码）VALUES（'Alice'，'704-555-1212'）
  ON CONFLICT（name）进行更新设置phonenumber = excluded.phonenumber;

在第二个示例中，DO UPDATE子句中的表达式的格式为“ excluded.phonenumber”。的“排除”。前缀会导致“电话号码”引用没有冲突时应插入的电话号码的值。因此，upsert的作用是插入Alice的电话号码（如果不存在），或者用新的电话号码覆盖Alice的任何先前电话号码。

请注意，DO UPDATE子句仅对在INSERT期间遇到约束错误的单行起作用。不必包含将操作限制为该行的WHERE子句。DO UPDATE末尾WHERE子句的唯一用途是根据原始值和/或新值有选择地将DO UPDATE更改为无操作。例如：

创建表电话簿2（
  名称TEXT PRIMARY KEY，
  电话号码TEXT，
  有效日期DATE
）;
插入电话簿2（姓名，电话号码，有效日期）
  值（'Alice'，'704-555-1212'，'2018-05-08'）
  ON CONFLICT（name）做更新集
    phonenumber = excluded.phonenumber，
    validDate = excluded.validDate
  在哪里排除.validDate> phonebook2.validDate;

在最后一个示例中，仅当新插入值的有效日期比表中已存在的条目新时，才更新phonebook2条目。如果表已包含具有相同名称和当前有效日期的条目，则WHERE子句将使DO UPDATE变为无操作。

2.2。解析歧义

当UPSERT附加到的INSERT语句从SELECT语句获取其值时，可能存在语法歧义。解析器可能无法判断“ ON”关键字是否正在引入UPSERT或它是否是联接的ON子句。若要解决此问题，即使该WHERE子句只是“ WHERE true”，SELECT语句也应始终包含WHERE子句。

模棱两可的使用ON：

插入到t1选择*从t2
在冲突（x）上执行更新集y = excluded.y;

使用WHERE子句解决的歧义：

在t1 SELECT * FROM t2中插入true
在冲突（x）上执行更新集y = excluded.y;

3.局限性

UPSERT当前不适用于虚拟表。

DO UPDATE子句的更新操作的冲突解决算法始终为ABORT。换句话说，该行为就像DO UPDATE子句实际上被写为“ DO UPDATE OR ABORT”一样。如果DO UPDATE子句遇到任何约束冲突，则整个INSERT语句将回滚并暂停。即使DO UPDATE子句包含在INSERT语句或指定其他冲突解决算法的触发器中，也是如此。

4.历史

UPSERT语法已添加到SQLite版本3.24.0（2018-06-04）中。原始实现严格遵循PostgreSQL语法，因为它只允许使用一个ON CONFLICT子句，并且需要在DO UPDATE上有一个冲突目标。在SQLite版本3.35.0（2021-03-12）中，对语法进行了通用化以允许多个ON CONFLICT子句并允许DO UPDATE解析而没有冲突目标。