小. 快速. 可靠.
选择任意三个.
UPSERT

1. Syntax

upsert-clause:

syntax diagram upsert-clause

column-name-list:

expr:

indexed-column:

2. Description

UPSERT是INSERT的特殊语法补充,如果INSERT违反唯一性约束,则它会使INSERT表现为UPDATE或no-op. UPSERT不是标准的SQL. SQLite中的UPSERT遵循PostgreSQL建立的语法. UPSERT语法已添加到SQLite版本3.24.0(2018-06-04)中.

UPSERT是普通的INSERT语句,后跟上面显示的特殊ON CONFLICT子句.

在" ON CONFLICT"和" DO"关键字之间出现的语法称为"冲突目标". 冲突目标指定了将触发更新的特定唯一性约束. 冲突目标对于DO UPDATE upsert是必需的,但对于DO NOTHING是可选的. 当省略冲突目标时,违反INSERT表上任何唯一性约束都会触发upsert行为.

如果插入操作将导致由冲突目标子句标识的唯一性约束失败,那么将省略插入操作,而是执行DO NOTHING或DO UPDATE操作. 对于多行刀片,该决定是针对刀片的每一行分别做出的.

特殊的UPSERT处理仅针对接收INSERT的表上的唯一性约束而发生. "唯一性约束"是CREATE TABLE语句中的显式UNIQUE或PRIMARY KEY约束,或者是唯一索引 . UPSERT不会针对失败的NOT NULL或外键约束或使用触发器实现的约束进行干预.

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

一些示例将有助于说明差异:

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". 的"排除". 前缀会导致"电话号码"引用没有冲突时应插入的电话号码的值. 因此,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;

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

2.1. Parsing Ambiguity

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

模棱两可的使用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. Limitations

UPSERT当前不适用于虚拟表 .

by  ICOPY.SITE