如何有条件地插入或替换 SQLite 中的一行? [英] How to conditionally INSERT OR REPLACE a row in SQLite?
问题描述
我想插入或替换_on_condition.如果不满足条件，请勿插入或更换.这可能吗?
I would like to insert or replace_on_condition. If the condition is not satisfied, do not insert or replace. Is this possible?
对于我的项目，我目前有两个数据收集过程.一个速度很快，但不能捕获所有内容.另一个很慢，但能抓住一切.通过快速处理，我几乎可以实时获取数据.使用慢速，我在一天结束时使用批处理获取数据.
For my project, I currently have two data collection processes. One is fast but doesn't catch everything. The other is slow but catches everything. With the fast process, I get data almost in real time. With the slow one I get data using a batch process at the end of day.
我的问题是:有时快速过程会在慢速过程之前完成"记录(意味着不再需要更新)，而在夜间批处理过程中，完成"记录会被缓慢进程的批量数据中的过时待处理"记录所取代.
My issue is this: sometimes the fast process will "Complete" a record (meaning it no longer needs to be updated) BEFORE the slow process, and later in the day during the nightly batch process, the "Complete" record will get replaced by an outdated "Pending" record found in the slow process's bulk data.
我想要的是类似于以下伪代码的条件检查:
What I would like is a conditional check that goes something like this pseudocode:
If(record_is_not_complete or does_not_exist)
{ INSERT or REPLACE; }
Else
{ do_nothing and move_to_the_next; }
如果我从一个标准的 INSERT OR REPLACE 示例开始:
If I begin with a standard INSERT OR REPLACE example:
INSERT OR REPLACE INTO UserProgress (id, status, level)
VALUES (1, 'COMPLETE', 5);
这应该会在 UserProgress 表中产生一行，其中包含条目 [1,COMPLETE,5].
Which should result in a row in UserProgress table with entry [1,COMPLETE,5].
如果出现以下情况:
INSERT OR REPLACE INTO UserProgress (id, status, level)
VALUES (1, 'PENDING', 4);
我希望它跳过这个，因为已经有一条 COMPLETE 记录.
I would like for it to skip this, because there is already a COMPLETE record.
我确定这是一个重复的问题.但真的是这样吗?这个问题有很多答案，我不确定哪个是最好的方法.看看我发现的所有这些例子:
I'm sure this is a duplicate question. But is it really? There are so many answers to this question I am not sure which is the best approach. Look at all these examples that I found:
我可以尝试添加 CASE 语句，我被告知它等同于 IF-THEN-ELSE 语句.如本例所示.
I can attempt to add a CASE statement, I have been told it is equivalent to a IF-THEN-ELSE statement. As done in this example.
我可以尝试在 VALUES 中使用 SELECT 或 COALESCE 语句.如本例所示.
I can attempt to use SELECT or COALESCE statement in the VALUES. As done in this example.
我什至可以尝试使用 SELECT WHERE 语句.如本例所示.
I can even attempt to use a SELECT WHERE statement. As done in this example.
我可以尝试使用 LEFT JOIN 语句.如本例所示.
I can attempt to use an LEFT JOIN statement. As done in this example.
这对 SQLite 来说很棒.似乎有多种方法可以给同一只猫剥皮.我是一个新手，我现在很困惑.目前尚不清楚我应该使用哪种方法.
Which is great for SQLite. There appears to be multiple ways to skin the same cat. Me being a novice I am now confused. It isn't clear which approach I should be using.
我正在寻找一种可以在一条 sql 语句中完成的解决方案.
I am looking for a solution that can be done in one sql statement.
* 更新 *
我找到了两个事务的解决方案.我仍在寻找单一交易解决方案.
I found a two transaction solution. I'm still on the hunt for a single transaction solution.
这可行，但使用两个事务:
This works, but uses two transactions:
public void Create(IEnumerable<UserProgress> items)
{
var sbFields = new StringBuilder();
sbFields.Append("ID,");
sbFields.Append("STATUS,");
sbFields.Append("LEVEL,");
int numAppended = 3;
var sbParams = new StringBuilder();
for (int i = 1; i <= numAppended; i++)
{
sbParams.Append("@param");
sbParams.Append(i);
if (i < numAppended)
{
sbParams.Append(", ");
}
}
// attempting this solution: https://stackoverflow.com/questions/2251699/sqlite-insert-or-replace-into-vs-update-where
// first insert the new stuff.
using (var command = new SQLiteCommand(Db))
{
command.CommandText = "INSERT OR IGNORE INTO USERPROGRESS (" + sbFields + ") VALUES(" + sbParams + ")";
command.CommandType = CommandType.Text;
using (var transaction = Db.BeginTransaction())
{
foreach (var user in items)
{
command.Parameters.Add(new SQLiteParameter("@param1", user.Id));
command.Parameters.Add(new SQLiteParameter("@param2", user.Status));
command.Parameters.Add(new SQLiteParameter("@param3", user.Level));
command.ExecuteNonQuery();
}
transaction.Commit();
}
}
using (var command = new SQLiteCommand(Db))
{
string parameterized = "";
for (int i = 1; i <= 3; i++)
{
parameterized += _columnNames[i - 1] + "=" + "@param" + i;
if (i != 3)
parameterized += ",";
}
command.CommandText = "UPDATE USERPROGRESS SET " + parameterized + " WHERE ID=@param1 AND STATUS !='COMPLETE'";
command.CommandType = CommandType.Text;
using (var transaction = Db.BeginTransaction())
{
foreach (var user in items)
{
command.Parameters.Add(new SQLiteParameter("@param1", user.Id));
command.Parameters.Add(new SQLiteParameter("@param2", user.Status));
command.Parameters.Add(new SQLiteParameter("@param3", user.Level));
command.ExecuteNonQuery();
}
transaction.Commit();
}
}
}
推荐答案
SQL
INSERT OR REPLACE INTO UserProgress (id, status, level)
SELECT '***id value***', '***status value***', '***level value***'
WHERE NOT EXISTS (SELECT * FROM UserProgress
WHERE id = '***id value***' AND status = 'COMPLETE');
(其中id值、状态值和级别值 酌情插入)
(where id value, status value and level value are inserted as appropriate)
演示
http://www.sqlfiddle.com/#!5/a9b82d/1
说明
EXISTS部分用于查找表中是否存在具有相同id且状态值为'COMPLETE'的现有行代码>.如果匹配此条件，则不执行任何操作(由于 WHERE NOT).否则，具有指定 id 的行要么在不存在时插入，要么在存在时使用指定的值更新(由于 INSERT OR REPLACE).
The EXISTS part is used to find out whether there is an existing row in the table with the same id whose status value is 'COMPLETE'. If this condition is matched, nothing is done (due to the WHERE NOT). Otherwise, the row with the specified id is either INSERTed if not present or UPDATEd with the specified values if present (due to the INSERT OR REPLACE).
这篇关于如何有条件地插入或替换 SQLite 中的一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
