嗨,我在SQlite3 上遇到了以下问题
我有一张简单的表
CREATE TABLE TestTable (id INT, cnt INT);
表中已有一些行。
我有一些数据要插入到表中:{(id0,cnt0(,(id1,cnt1(…}
我想在表中插入数据,在id冲突时,更新TestTable.cnt=TestTable.cnt+value.cnt
(values.cnt是cnt0,cnt1…基本上是我要插入的数据(
***但问题是,id没有主要或唯一的约束,我不允许更改它!
我目前拥有的:
在我的程序中,我循环浏览的所有值
UPDATE TestTABLE SET count = count + value.cnt WHERE id = value.id;
if (sqlite3_changes() == 0)
INSERT INTO MyTable (id, cnt) values (value.id, value.cnt);
但问题是,对于一个非常大的数据集,为每个数据条目进行2次查询的时间太长。我正在尝试将多个条目捆绑到一个调用中。
如果你对我的描述有疑问,请告诉我,谢谢你的帮助!
如果您能够创建临时表,请执行以下操作。虽然我没有在这里显示,但我建议将所有这些都封装在一个交易中。即使您还可以添加临时唯一索引,这种技术也可能提高效率。(在这种情况下,您可以在临时表中使用具有源数据的UPSERT。(
CREATE TEMP TABLE data(id INT, cnt INT);
现在,将新数据插入到临时表中,无论是使用主机语言数据库还是制作类似的插入语句
INSERT INTO data (id, cnt)
VALUES (1, 100),
(2, 200),
(5, 400),
(7, 500);
现在使用单个update语句更新所有现有行。SQLite没有一个方便的语法来连接表和/或为UPDATE语句提供源查询。然而,可以使用嵌套语句来提供类似的便利:
UPDATE TestTable AS tt
SET cnt = cnt + ifnull((SELECT cnt FROM data WHERE data.id == tt.id), 0)
WHERE tt.id IN (SELECT id FROM data);
请注意,这两个嵌套查询是相互独立的。事实上,对于这种简单的情况,可以完全消除WHERE子句并获得相同的结果。WHERE子句只是为了提高效率,只尝试更新匹配的id。SET子句中的另一个子查询也指定了id
上的匹配,但仅此一个子查询仍允许更新不匹配的行,默认为null
值,并(通过isnull()
函数(转换为0以获得no-op。顺便说一句,如果没有isnull()
函数,总和将导致null并覆盖非null值。
最后,只插入不存在id
值的行:
INSERT INTO TestTable (id, cnt)
SELECT data.id, data.cnt
FROM data LEFT JOIN TestTable
ON data.id == TestTable.id
WHERE TestTable.id IS NULL;