UPSERT *不是* INSERT 或 REPLACE

Question

http://en.wikipedia.org/wiki/Upsert

在 SQL Server 上插入更新存储过程

有没有一些我没有想到的聪明的方法可以在 SQLite 中执行此操作的？

基本上，如果记录存在，我想更新四列中的三列。 如果它不存在，我想插入第四列具有默认（NULL）值的记录。

ID 是主键，因此只有一条记录需要 UPSERT。

（我试图避免 SELECT 的开销，以确定是否需要更新或插入显然）

建议？

---

我无法在 SQLite 站点上确认 TABLE CREATE 的语法。 我还没有构建一个演示来测试它，但似乎不支持它。

如果是的话，我有三列，所以它实际上看起来像：

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    Blob1 BLOB ON CONFLICT REPLACE, 
    Blob2 BLOB ON CONFLICT REPLACE, 
    Blob3 BLOB 
);

但是前两个 blob 不会引起冲突，只有 ID 会引起冲突 所以我假设当绑定数据是一个完整的事务时，Blob1和Blob2不会被替换（根据需要）

---

SQLite中的更新，这意味着 要更新的每个发送行都需要： 与允许使用重置功能的 INSERT 不同，Prepare/Bind/Step/Finalize 语句

语句对象的生命周期如下：

1. 使用 sqlite3_prepare_v2() 创建对象
2. 将值绑定到主机参数使用 sqlite3_bind_ 接口。
3. 通过调用 sqlite3_step() 运行 SQL
4. 使用 sqlite3_reset() 重置语句，然后返回到步骤 2 并重复。
5. 使用 sqlite3_finalize() 销毁语句对象。

我猜 UPDATE 比 INSERT 慢，但是它与使用主键的 SELECT 相比如何？

也许我应该使用 select 读取第 4 列 (Blob3)，然后使用 REPLACE 写入一条新记录，将原始第 4 列与前 3 列的新数据混合？

Answer 1

假设表中有三列：ID、NAME、ROLE

---

BAD: 这将插入或替换所有列为 ID=1 的新值：

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

---

BAD: 这将插入或替换2 列...NAME 列将设置为 NULL 或默认值：

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

---

GOOD：使用 SQLite On 冲突子句
SQLite 中的 UPSERT 支持！ UPSERT 语法已添加到 SQLite 3.24.0 版本中！

UPSERT 是 INSERT 的一种特殊语法补充，如果 INSERT 违反唯一性约束，则该语法会导致 INSERT 表现为 UPDATE 或无操作。 UPSERT 不是标准 SQL。 SQLite 中的 UPSERT 遵循 PostgreSQL 建立的语法。

很好但乏味：这将更新其中 2 列。
当ID=1存在时，NAME不受影响。
当ID=1不存在时，名称将为默认值（NULL）。

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

这将更新其中 2 列。
当ID=1存在时，ROLE不受影响。
当ID=1不存在时，角色将被设置为“Benchwarmer”而不是默认值。

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

Answer 2

INSERT OR REPLACE不等同于“UPSERT”。

假设我有一张 Employee 表，其中包含字段 id、name 和 role：

INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO")
INSERT OR REPLACE INTO Employee ("id", "role") VALUES (1, "code monkey")

Boom，您丢失了员工编号 1 的姓名。SQLite 已将其替换为默认值。

UPSERT 的预期输出是更改角色并保留名称。

Answer 3

如果您只想保留现有行中的一列或两列，Eric B 的答案是可以的。 如果你想保留很多列，它很快就会变得太麻烦。

这是一种可以很好地扩展到任意数量的列的方法。 为了说明这一点，我将假设以下模式：

 CREATE TABLE page (
     id      INTEGER PRIMARY KEY,
     name    TEXT UNIQUE,
     title   TEXT,
     content TEXT,
     author  INTEGER NOT NULL REFERENCES user (id),
     ts      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );

特别注意 name 是行的自然键 - id 仅用于外键，所以重点是SQLite 在插入新行时选择 ID 值本身。 但是，当根据其名称更新现有行时，我希望它继续具有旧的 ID 值（显然！）。

我使用以下构造实现了真正的 UPSERT：

 WITH new (name, title, author) AS ( VALUES('about', 'About this site', 42) )
 INSERT OR REPLACE INTO page (id, name, title, content, author)
 SELECT old.id, new.name, new.title, old.content, new.author
 FROM new LEFT JOIN page AS old ON new.name = old.name;

该查询的确切形式可能略有不同。 关键是使用带有左外连接的 INSERT SELECT 来将现有行连接到新值。

在这里，如果一行以前不存在，old.id将为NULL，然后SQLite将自动分配一个ID，但如果已经存在这样的行，old.id 将具有实际值，并且该值将被重用。 这正是我想要的。

事实上，这是非常灵活的。 请注意 ts 列在所有方面都完全缺失 - 因为它有一个 DEFAULT 值，SQLite 在任何情况下都会做正确的事情，所以我没有我自己来照顾它。

您还可以在new 和old 两侧都包含一列，然后在COALESCE(new.content, old.content) 中使用外部 SELECT 表示“如果有新内容，则插入新内容，否则保留旧内容”——例如，如果您使用固定查询并将新值与占位符绑定。

Answer 4

此答案已更新，因此以下评论不再适用。

2018-05-18停止按。

SQLite 中的 UPSERT 支持！ UPSERT 语法已添加到 SQLite 3.24.0 版本（待定）！

UPSERT 是 INSERT 的一种特殊语法补充，如果 INSERT 违反唯一性约束，则该语法会导致 INSERT 表现为 UPDATE 或无操作。 UPSERT 不是标准 SQL。 SQLite 中的 UPSERT 遵循 PostgreSQL 建立的语法。

或者：

另一种完全不同的方法：在我的应用程序中，当我在内存中创建行时，我将内存中的 rowID 设置为 long.MaxValue 。 （MaxValue 永远不会被用作 ID，你活得不够长......）然后，如果 rowID 不是该值，那么它必须已经在数据库中，因此需要更新，如果它是 MaxValue，则需要插入。 仅当您可以跟踪应用程序中的 rowID 时，这才有用。

Answer 5

如果您通常进行更新，我会..

1. 开始一个事务
2. 进行更新
3. 检查行数
4. 如果为 0，则执行插入
5. 提交

如果您通常进行插入，我会

1. 开始一个事务
2. 尝试插入
3. 检查主键违规错误
4. （如果出现）更新时发生错误
5. 提交

这样您就可以避免选择并且在 Sqlite 上事务性良好。

Answer 6

我意识到这是一个旧线程，但我最近一直在 sqlite3 中工作，并想出了这种方法，它更适合我动态生成参数化查询的需求：

insert or ignore into <table>(<primaryKey>, <column1>, <column2>, ...) values(<primaryKeyValue>, <value1>, <value2>, ...); 
update <table> set <column1>=<value1>, <column2>=<value2>, ... where changes()=0 and <primaryKey>=<primaryKeyValue>; 

它仍然是 2 个带有更新的 where 子句的查询，但似乎确实如此窍门。 我的脑海中也有这样的愿景：如果对changes()的调用大于零，sqlite可以完全优化掉更新语句。 我不知道它是否真的如此，但人可以做梦，不是吗？ ;)

对于奖励积分，您可以附加此行，它会返回行的 ID，无论它是新插入的行还是现有行。

select case changes() WHEN 0 THEN last_insert_rowid() else <primaryKeyValue> end;

Answer 7

从版本 3.24.0 开始，SQLite 支持 UPSERT。

来自文档：

UPSERT 是 INSERT 的一种特殊语法补充，如果 INSERT 违反唯一性约束，它会导致 INSERT 表现为 UPDATE 或无操作。 UPSERT 不是标准 SQL。 SQLite 中的 UPSERT 遵循 PostgreSQL 建立的语法。UPSERT 语法已添加到 SQLite 3.24.0 版本（待定）。

UPSERT 是一个普通的 INSERT 语句，后跟特殊的 ON CONFLICT 子句

^{图像来源：https://www.sqlite.org/images/syntax/upsert-clause.gif}

---

示例：

CREATE TABLE t1(id INT PRIMARY KEY, c TEXT);
INSERT INTO t1(id, c) VALUES (1,'a'), (2, 'b');
SELECT * FROM t1;


INSERT INTO t1(id, c) VALUES (1, 'c');
-- UNIQUE constraint failed: t1.id

INSERT INTO t1(id, c) VALUES (1, 'c')
ON CONFLICT DO NOTHING;

SELECT * FROM t1;

INSERT INTO t1(id, c)
VALUES (1, 'c')
ON CONFLICT(id) DO UPDATE SET c = excluded.c;

SELECT * FROM t1;

db<>fiddle 演示

Answer 8

Bernhardt 的更新：

您确实可以在 SQLite 中执行更新插入，只是看起来与您习惯的有点不同。 它看起来像：

INSERT INTO table_name (id, column1, column2) 
VALUES ("youruuid", "value12", "value2")
ON CONFLICT(id) DO UPDATE 
SET column1 = "value1", column2 = "value2"

Answer 9

这是一个实际上是 UPSERT（更新或插入）而不是插入或替换（在许多情况下工作方式不同）的解决方案。

它的工作原理如下：
1. 如果存在相同Id的记录则尝试更新。
2. 如果更新未更改任何行 (NOT EXISTS(SELECTchanges() ASchangeFROMContactWHEREchange<>0))，则插入记录。

因此，要么更新现有记录，要么执行插入。

重要的细节是使用changes() SQL函数来检查更新语句是否命中任何现有记录，并且仅在没有命中任何记录时才执行插入语句。

需要提到的一件事是，changes() 函数不会返回由较低级别触发器执行的更改（请参阅 http: //sqlite.org/lang_corefunc.html#changes），所以一定要考虑到这一点。

这是 SQL...

测试更新：

--Create sample table and records (and drop the table if it already exists)
DROP TABLE IF EXISTS Contact;
CREATE TABLE [Contact] (
  [Id] INTEGER PRIMARY KEY, 
  [Name] TEXT
);
INSERT INTO Contact (Id, Name) VALUES (1, 'Mike');
INSERT INTO Contact (Id, Name) VALUES (2, 'John');

-- Try to update an existing record
UPDATE Contact
SET Name = 'Bob'
WHERE Id = 2;

-- If no record was changed by the update (meaning no record with the same Id existed), insert the record
INSERT INTO Contact (Id, Name)
SELECT 2, 'Bob'
WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0);

--See the result
SELECT * FROM Contact;

测试插入：

--Create sample table and records (and drop the table if it already exists)
DROP TABLE IF EXISTS Contact;
CREATE TABLE [Contact] (
  [Id] INTEGER PRIMARY KEY, 
  [Name] TEXT
);
INSERT INTO Contact (Id, Name) VALUES (1, 'Mike');
INSERT INTO Contact (Id, Name) VALUES (2, 'John');

-- Try to update an existing record
UPDATE Contact
SET Name = 'Bob'
WHERE Id = 3;

-- If no record was changed by the update (meaning no record with the same Id existed), insert the record
INSERT INTO Contact (Id, Name)
SELECT 3, 'Bob'
WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0);

--See the result
SELECT * FROM Contact;

Answer 10

我所知道的最好的方法是先进行更新，然后进行插入。
“选择的开销”是必要的，但这并不是一个可怕的负担，因为您正在主键上搜索，速度很快。

您应该能够使用您的表和修改以下语句： 字段名称来执行您想要的操作。

--first, update any matches
UPDATE DESTINATION_TABLE DT
SET
  MY_FIELD1 = (
              SELECT MY_FIELD1
              FROM SOURCE_TABLE ST
              WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY
              )
 ,MY_FIELD2 = (
              SELECT MY_FIELD2
              FROM SOURCE_TABLE ST
              WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY
              )
WHERE EXISTS(
            SELECT ST2.PRIMARY_KEY
            FROM
              SOURCE_TABLE ST2
             ,DESTINATION_TABLE DT2
            WHERE ST2.PRIMARY_KEY = DT2.PRIMARY_KEY
            );

--second, insert any non-matches
INSERT INTO DESTINATION_TABLE(
  MY_FIELD1
 ,MY_FIELD2
)
SELECT
  ST.MY_FIELD1
 ,NULL AS MY_FIELD2  --insert NULL into this field
FROM
  SOURCE_TABLE ST
WHERE NOT EXISTS(
                SELECT DT2.PRIMARY_KEY
                FROM DESTINATION_TABLE DT2
                WHERE DT2.PRIMARY_KEY = ST.PRIMARY_KEY
                );

Answer 11

扩展亚里士多德的答案，您可以从虚拟“单例”表（您自己创建的单行表）中进行选择。 这避免了一些重复。

我还使该示例在 MySQL 和 SQLite 之间可移植，并使用“date_added”列作为如何仅在第一次设置列的示例。

 REPLACE INTO page (
   id,
   name,
   title,
   content,
   author,
   date_added)
 SELECT
   old.id,
   "about",
   "About this site",
   old.content,
   42,
   IFNULL(old.date_added,"21/05/2013")
 FROM singleton
 LEFT JOIN page AS old ON old.name = "about";

Answer 12

如果有人想阅读我在 Cordova 中的 SQLite 解决方案，感谢上面的@david 回答，我得到了这个通用的 js 方法。

function    addOrUpdateRecords(tableName, values, callback) {
get_columnNames(tableName, function (data) {
    var columnNames = data;
    myDb.transaction(function (transaction) {
        var query_update = "";
        var query_insert = "";
        var update_string = "UPDATE " + tableName + " SET ";
        var insert_string = "INSERT INTO " + tableName + " SELECT ";
        myDb.transaction(function (transaction) {
            // Data from the array [[data1, ... datan],[()],[()]...]:
            $.each(values, function (index1, value1) {
                var sel_str = "";
                var upd_str = "";
                var remoteid = "";
                $.each(value1, function (index2, value2) {
                    if (index2 == 0) remoteid = value2;
                    upd_str = upd_str + columnNames[index2] + "='" + value2 + "', ";
                    sel_str = sel_str + "'" + value2 + "', ";
                });
                sel_str = sel_str.substr(0, sel_str.length - 2);
                sel_str = sel_str + " WHERE NOT EXISTS(SELECT changes() AS change FROM "+tableName+" WHERE change <> 0);";
                upd_str = upd_str.substr(0, upd_str.length - 2);
                upd_str = upd_str + " WHERE remoteid = '" + remoteid + "';";                    
                query_update = update_string + upd_str;
                query_insert = insert_string + sel_str;  
                // Start transaction:
                transaction.executeSql(query_update);
                transaction.executeSql(query_insert);                    
            });
        }, function (error) {
            callback("Error: " + error);
        }, function () {
            callback("Success");
        });
    });
});
}

因此，首先使用此函数获取列名称：

function get_columnNames(tableName, callback) {
myDb.transaction(function (transaction) {
    var query_exec = "SELECT name, sql FROM sqlite_master WHERE type='table' AND name ='" + tableName + "'";
    transaction.executeSql(query_exec, [], function (tx, results) {
        var columnParts = results.rows.item(0).sql.replace(/^[^\(]+\(([^\)]+)\)/g, '$1').split(','); ///// RegEx
        var columnNames = [];
        for (i in columnParts) {
            if (typeof columnParts[i] === 'string')
                columnNames.push(columnParts[i].split(" ")[0]);
        };
        callback(columnNames);
    });
});
}

然后以编程方式构建事务。

“Values”是您之前应该构建的数组，它代表您想要插入或更新到表中的行。

“remoteid”是我用作参考的 ID，因为我正在与远程服务器同步。

SQLite Cordova插件的使用请参考官方链接

Answer 13

遵循亚里士多德Pagaltzis以及来自Eric B 的回答，这里是一个更新插入选项，用于仅更新几列或插入整行（如果不存在）。

在这种情况下，假设应该更新标题和内容，在存在时保留其他旧值，并在找不到名称时插入提供的值：

NOTE id 强制为 NULL当 INSERT 时，因为它应该是自动增量。 如果它只是生成的主键，则也可以使用COALESCE（请参阅亚里士多德·帕加尔茨评论）。

WITH new (id, name, title, content, author)
     AS ( VALUES(100, 'about', 'About this site', 'Whatever new content here', 42) )
INSERT OR REPLACE INTO page (id, name, title, content, author)
SELECT
     old.id, COALESCE(old.name, new.name),
     new.title, new.content,
     COALESCE(old.author, new.author)
FROM new LEFT JOIN page AS old ON new.name = old.name;

因此，一般规则是，如果您想保留旧值，请使用COALESCE，当您想更新值时，请使用new.fieldname

Answer 14

我想这可能就是您正在寻找的：ON CONFLICT 条款。

如果你像这样定义你的表：

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    field1 TEXT 
); 

现在，如果你使用已经存在的 id 执行 INSERT，SQLite 会自动执行 UPDATE 而不是 INSERT。

呃……

Answer 15

此方法重新混合了该问题答案中的一些其他方法，并结合了 CTE（通用表表达式）的使用。 我将介绍该查询，然后解释为什么我这样做。

如果有员工 300，我想将员工 300 的姓氏更改为 DAVIS。否则，我将添加一名新员工。

表名称：员工
列：id、first_name、last_name

查询是：

INSERT OR REPLACE INTO employees (employee_id, first_name, last_name)
WITH registered_employees AS ( --CTE for checking if the row exists or not
    SELECT --this is needed to ensure that the null row comes second
        *
    FROM (
        SELECT --an existing row
            *
        FROM
            employees
        WHERE
            employee_id = '300'

        UNION

        SELECT --a dummy row if the original cannot be found
            NULL AS employee_id,
            NULL AS first_name,
            NULL AS last_name
    )
    ORDER BY
        employee_id IS NULL --we want nulls to be last
    LIMIT 1 --we only want one row from this statement
)
SELECT --this is where you provide defaults for what you would like to insert
    registered_employees.employee_id, --if this is null the SQLite default will be used
    COALESCE(registered_employees.first_name, 'SALLY'),
    'DAVIS'
FROM
    registered_employees
;

基本上，我使用 CTE 来减少必须使用 select 语句来确定默认值的次数。 由于这是一个 CTE，我们只需从表中选择我们想要的列，INSERT 语句就会使用它。

现在，您可以通过将 COALESCE 函数中的空值替换为应有的值来决定要使用的默认值。

Answer 16

如果您不介意分两次执行此操作。

步骤：

1) 使用“INSERT OR IGNORE”添加新项目

2) 使用“UPDATE”更新现有项目 这

两个步骤的输入都是新项目或可更新项目的同一集合。 适用于无需更改的现有项目。 它们将被更新，但数据相同，因此最终结果没有变化。

当然，速度较慢等等，效率低下。 是的。

sql写起来容易维护和理解吗？ 确实。

这是一个需要考虑的权衡。
非常适合小型更新。 对于那些不介意为了代码可维护性而牺牲效率的人来说非常有用。

Answer 17

使用 WHERE 选择更新的日期记录的更新插入的完整示例。

-- https://www.db-fiddle.com/f/7jyj4n76MZHLLk2yszB6XD/22
 
DROP TABLE IF EXISTS db;

CREATE TABLE db
(
 id PRIMARY KEY,
 updated_at,
 other
);

-- initial INSERT
INSERT INTO db (id,updated_at,other) VALUES(1,1,1);

SELECT * FROM db;

-- INSERT without WHERE
INSERT INTO db (id,updated_at,other) VALUES(1,2,2)
ON CONFLICT(id) DO UPDATE SET updated_at=excluded.updated_at;

SELECT * FROM db;

-- WHERE is FALSE
INSERT INTO db (id,updated_at,other) VALUES(1,2,3)
ON CONFLICT(id) DO UPDATE SET updated_at=excluded.updated_at, other=excluded.other
WHERE excluded.updated_at > updated_at;

SELECT * FROM db;

-- ok to SET a PRIMARY KEY. WHERE is TRUE
INSERT INTO db (id,updated_at,other) VALUES(1,3,4)
ON CONFLICT(id) DO UPDATE SET id=excluded.id, updated_at=excluded.updated_at, other=excluded.other
WHERE excluded.updated_at > updated_at;

SELECT * FROM db;

Answer 18

刚刚读完这篇文章，并对这个“UPSERT”操作并不容易感到失望，我进一步调查了......

实际上，您可以在 SQLITE 中直接轻松地完成此操作。

而不是使用：INSERT INTO

使用：INSERT OR REPLACE INTO

这正是您想要的！

Answer 19

SELECT COUNT(*) FROM table1 WHERE id = 1;

如果 COUNT(*) = 0

INSERT INTO table1(col1, col2, cole) VALUES(var1,var2,var3);

否则如果 COUNT(*) > 0

UPDATE table1 SET col1 = var4, col2 = var5, col3 = var6 WHERE id = 1;