SQLite-UPSERT * not * INSERT or REPLACE SQLite-UPSERT * not * 插入或替换

小开

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

你应该可以用你的表格&字段名称来做您想做的事情。

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

小开

我想这可能就是你要找的:关于冲突条款。

如果你这样定义你的表:

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

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

Hth……

小开

如果你通常做更新，我会..

开始事务
进行更新
检查行数
如果是0，则插入
提交

如果你通常做插入，我会

开始事务
尝试插入
检查主键违反错误
如果出现错误，就进行更新
提交

这样你就避免了选择，你在Sqlite上的事务是健全的。

小开

刚刚读了这篇文章，很失望地发现，要找到这个“UPSERT”并不容易，我进一步调查了一下……

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

而不是使用:INSERT INTO

用途:# EYZ0

这正是你想要它做的!

小开

插入或替换是不相当于“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的预期输出是更改角色并保留名称。

小开

最佳答案

假设表中有三列: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');

好:对冲突子句使用SQLite 在SQLite中支持UPSERT ! UPSERT语法在3.24.0版本添加到SQLite !< / p >

UPSERT是INSERT的一个特殊语法，如果INSERT违反唯一性约束，它会导致INSERT行为为UPDATE或no-op。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')
);

小开

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

小开

如果您只想从现有行中保留一个或两个列，则可以使用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值本身。但是，当基于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)来表示“如果有任何新内容，则插入新内容，否则保留旧内容”——例如，如果你正在使用固定查询并将新值与占位符绑定。

小开

在亚里士多德的回答上展开，你可以从一个虚拟的“单例”表(一个你自己创建的单行表)中选择。这避免了一些重复。

我还保留了示例在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";

小开

这里有一个解决方案，它实际上是UPSERT (UPDATE或INSERT)，而不是INSERT或REPLACE(在许多情况下工作方式不同)。

它是这样工作的:
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;

小开

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

如果# EYZ0

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

else if COUNT(*) > 0

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

小开

这个答案已经更新了，所以下面的评论不再适用。

2018-05-18停止按。

在SQLite中支持UPSERT ! UPSERT语法已添加到SQLite 3.24.0版本(待定)!

UPSERT是INSERT的一个特殊语法，如果INSERT违反唯一性约束，它会导致INSERT行为为UPDATE或no-op。UPSERT不是标准SQL。SQLite中的UPSERT遵循PostgreSQL建立的语法。

另外:

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

小开

这种方法混合了回答这个问题的一些其他方法，并结合了CTE(公共表表达式)的使用。我将介绍这个查询，然后解释我为什么这么做。

如果有员工300的话，我想把员工300的姓改成DAVIS。否则，我将增加一个新员工。

表名:employees 列: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函数中的空值替换为值来决定要使用的缺省值。

小开

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

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插件的使用，请参考官方的链接

小开

遵循亚里士多德Pagaltzis和COALESCE从Eric B的回答的思想，这里是一个upsert选项，只更新少数列或插入完整的行，如果它不存在。

在这种情况下，想象一下标题和内容应该被更新，当存在时保持其他旧值，当没有找到name时插入提供的值:

当INSERT时，请注意 id被强制为NULL，因为它应该是自动递增的。如果它只是一个生成的主键，那么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

小开

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

文档:

UPSERT是INSERT的一个特殊语法，如果INSERT违反唯一性约束，它会导致INSERT行为为UPDATE或no-op。UPSERT不是标准SQL。SQLite中的UPSERT遵循PostgreSQL建立的语法。 UPSERT语法被添加到3.24.0版本的SQLite(待定)。

UPSERT是一个普通的INSERT语句，后面跟着特殊的ON冲突子句

^{图片来源: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<>小提琴demo

小开

伯恩哈特的最新消息:

你确实可以在SQLite中做upsert，只是看起来与你习惯的有点不同。它看起来像这样:

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

小开

如果你不介意分两次操作的话。

步骤:

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

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

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

当然，更慢，等等。效率低下。是的。

易于编写sql和维护和理解它?肯定。

这是需要权衡的。适用于小的上露。对于那些不介意为代码可维护性牺牲效率的人来说非常有用

小开

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

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