SQLite中文网
更新插入

1.语法

更新子句：

ON CONFLICT ( indexed-column ) WHERE expr DO , conflict target UPDATE SET column-name-list = expr WHERE expr NOTHING , column-name

列名列表：

表达式：

索引列：

2.说明

UPSERT 是添加到INSERT的子句，如果 INSERT 违反唯一性约束，它会导致 INSERT 表现为UPDATE或空操作。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 或外键约束或使用触发器实现的约束。

DO UPDATE 表达式中的列名指的是在尝试 INSERT 之前列的原始未更改值。要使用在约束未失败的情况下本应插入的值，请添加特殊的“排除”。列名的表限定符。

2.1. 例子

一些示例将有助于说明 UPSERT 的工作原理： 

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;

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

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;

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

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

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;

在最后一个示例中，仅当新插入值的 validDate 比表中已有的条目更新时，phonebook2 条目才会更新。如果该表已包含具有相同名称和当前 validDate 的条目，则 WHERE 子句会导致 DO UPDATE 变为空操作。

2.2. 解析歧义

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

ON 的使用不明确： 

INSERT INTO t1 SELECT * FROM t2
ON CONFLICT(x) DO UPDATE SET y=excluded.y;

使用 WHERE 子句解决歧义： 

INSERT INTO t1 SELECT * FROM t2 WHERE true
ON CONFLICT(x) DO UPDATE SET 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 解析。