如果找到记录，则在 SQLite 中使用运行总数进行更新 [英] Upsert in SQLite with running total if record is found

问题描述

我想在 SQLite 中将数据 upsert 到一个表，但正在更新的列(如果找到记录)必须是一个运行总数.我正在使用参数化查询，但语法有问题.

I would like to upsert data to a table in SQLite, but the column being updated (if the record is found) must be a running total. I am using a parameterized query, and am having trouble with the syntax.

到目前为止，我的理解是 COALESCE 是在 SQLite 中执行 upsert 的方法，所以这是我迄今为止所获得的框架:

My understanding thus far is that COALESCE is the way to do an upsert in SQLite, so this is the skeleton of what I've got thus far:

INSERT OR REPLACE INTO TABLE (ID, Quantity) 
VALUES (:ID, COALESCE(Quantity + :Quantity_change, :Quantity_change) 

也就是说，如果找到记录，则将数量变化添加到当前数量，否则插入数量变化的记录作为数量编号.

In other words, if the record is found, add the quantity change to the current quantity, else insert the record with the quantity change as the quantity number.

在 SQL Server 中，它与合并查询完美配合:

In SQL server it's working perfectly with a merge query:

MERGE ......
.....
WHEN MATCHED THEN UPDATE SET Quantity = Quantity + :Quantity_change
WHEN NOT MATCHED THEN INSERT (..Quantity), VALUES (.. :Quantity_change); 

推荐答案

好的，这对我有用:

INSERT OR REPLACE INTO TABLE (ID, Quantity) 
VALUES (:ID, 
        COALESCE(
          (SELECT Quantity FROM TABLE WHERE ID = :ID) + :Quantity_change, :Quantity_change));

感谢这个帮助链接.

这篇关于如果找到记录，则在 SQLite 中使用运行总数进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！