SQLite - UPSERT *not* INSERT 或 REPLACE

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

坏：这将使用 ID=1 的新值插入或替换所有列：

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

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

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

GOOD：使用 SQLite On 冲突子句 UPSERT support in SQLite! UPSERT 语法已添加到版本 3.24.0 的 SQLite！

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

https://i.stack.imgur.com/h475O.gif

好但乏味：这将更新 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')
          );

---

插入或替换不等同于“UPSERT”。

假设我有包含字段 id、name 和 role 的表 Employee：

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

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

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

---

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

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

 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 值。但是当基于其 name 更新现有行时，我希望它继续具有旧的 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 两侧都包含一列，然后在外部 SELECT 中使用例如 COALESCE(new.content, old.content) 表示“如果有任何内容，请插入新内容，否则保留旧内容” - 例如如果您使用的是固定查询并将新值与占位符绑定。

---

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

2018-05-18 停止新闻。

UPSERT support in SQLite! UPSERT 语法已添加到 SQLite 版本 3.24.0（待定）！

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

https://i.stack.imgur.com/h475O.gif

或者：

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

---

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

开始一个事务 做更新 检查行数 如果是 0 做插入 提交

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

开始事务尝试插入检查主键违规错误如果我们收到错误执行更新提交

这样你就可以避免选择，并且你在 Sqlite 上的事务是健全的。

---

我意识到这是一个旧线程，但我最近一直在使用 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;

---

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

从 documentation：

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

^{图片来源：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 demo

---

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

它的工作原理如下：
1. 如果存在具有相同 Id 的记录，请尝试更新。
2. 如果更新没有更改任何行 (NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 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;

---

伯恩哈特的更新：

你确实可以在 SQLite 中做一个 upsert，它只是看起来和你习惯的有点不同。它看起来像：

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

---

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

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

--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
                );

---

在 Aristotle’s answer 上展开，您可以从一个虚拟的“单例”表（您自己创建的单行表）中进行 SELECT。这避免了一些重复。

我还使示例在 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";

---

如果有人想阅读我在 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);
    });
});
}

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

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

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

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

---

我认为这可能是您正在寻找的：ON CONFLICT clause。

如果你这样定义你的表：

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

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

嗯...

---

此方法重新混合了此问题答案中的一些其他方法，并结合了 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 函数中将空值替换为应该使用的值来决定要使用的默认值。

---

遵循 Aristotle Pagaltzis 和 Eric B’s answer 中的 COALESCE 的想法，这里是一个 upsert 选项，仅更新少数列或插入整行（如果不存在）。

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

注意 id 在 INSERT 时被强制为 NULL，因为它应该是自动增量的。如果它只是一个生成的主键，那么也可以使用 COALESCE（参见 Aristotle Pagaltzis comment）。

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

---

如果您不介意在两个操作中执行此操作。

脚步：

1）使用“插入或忽略”添加新项目

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

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

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

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

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

---

使用 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;

---

刚刚阅读了这个帖子，并对这个“UPSERT”并不容易感到失望，我进一步调查了......

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

而不是使用：INSERT INTO

使用：INSERT OR REPLACE INTO

这正是你想要它做的！

---

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;

---