如何在SQLite中有条件地插入或替换行? [英] How to conditionally INSERT OR REPLACE a row in SQLite?
问题描述
我想插入或替换条件.如果不满足条件，请勿插入或更换.这可能吗?
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或REPLACE INTO UserProgress(ID，状态，级别)SELECT id值 ，' 状态值 '， 级别值 不存在的地方(选择*来自UserProgressid = id值 AND状态='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 的行(如果不存在，则被INSERTed)，或者如果存在则被指定的值更新(由于 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屋！
