SQLite插入和替换条件 [英] SQLite Insert and Replace with condition

问题描述

我不知道如何查询SQLite。需要
：
1）如果条件（比较新旧字段条目）
，则替换记录（主键）
2）如果数据库中不存在这样的条目，请插入一个条目

I can not figure out how to query a SQLite. needed: 1) Replace the record (the primary key), if the condition (comparison of new and old fields entries) 2) Insert an entry if no such entry exists in the database on the primary key.

重要的是，它必须运行非常快！
我无法提出有效的查询。

Importantly, it has to work very fast! I can not come up with an effective inquiry.

编辑。

MyInsertRequest -所需的表达式。

脚本：

CREATE TABLE testtable (a INT PRIMARY KEY, b INT, c INT)

INSERT INTO testtable VALUES (1, 2, 3)

select * from testtable
1|2|3

-- Adds an entry, because the primary key is not
++ MyInsertRequest VALUES (2, 2, 3) {if c>4 then replace}

select * from testtable
1|2|3
2|2|3

-- Adds
++ MyInsertRequest VALUES (3, 8, 3) {if c>4 then replace}

select * from testtable
1|2|3
2|2|3
3|8|3

-- Does nothing, because such a record (from primary key field 'a') 
-- is in the database and none c>4
++ MyInsertRequest VALUES (1, 2, 3) {if c>4 then replace}

select * from testtable
1|2|3
2|2|3
3|8|3

-- Does nothing
++ MyInsertRequest VALUES (3, 34, 3) {if c>4 then replace}

select * from testtable
1|2|3
2|2|3
3|8|3

-- replace, because such a record (from primary key field 'a') 
-- is in the database and c>2
++ MyInsertRequest VALUES (3, 34, 1) {if c>2 then replace}

select * from testtable
1|2|3
2|2|3
3|34|1

推荐答案

我尝试了其他答案

这应该可行，但是我不确定性能会带来什么影响。我相信您可能需要第一列作为主键是唯一的，否则它将每次仅插入一条新记录。

This should work, however I am unsure about the performance implications. I believe that you may need the first column to be unique as a primary key else it will simply insert a new record each time.

INSERT OR REPLACE INTO sometable
    SELECT columnA, columnB, columnC FROM (
    SELECT columnA, columnB, columnC, 1 AS tmp FROM sometable
        WHERE sometable.columnA = 1 AND
              sometable.columnB > 9
    UNION
        SELECT 1 AS columnA, 1 As columnB, 404 as columnC, 0 AS tmp)
    ORDER BY tmp DESC
    LIMIT 1

在这种情况下，将执行一个虚拟查询并将其合并到第二个查询，这将对性能产生影响，具体取决于查询方式写入以及如何对表建立索引。下一个性能问题可能会影响结果的排序和限制。但是，我希望第二个查询仅返回一条记录，因此不会对性能造成太大影响。

In this case one dummy query is executed and union-ed onto a second query which would have a performance impact depending on how it is written and how the table is indexed. The next performance problem has potential where the results are ordered and limited. However, I expect that the second query should only return one record and therefore it should not be too much of a performance hit.

您还可以省略 ORDER BY tmp LIMIT 1 并与我的sqlite版本一起使用，但可能会影响性能，因为它最终可能会更新记录两次（写入原始值，然后写入新值（如果适用））。

You can also omit the ORDER BY tmp LIMIT 1 and it works with my version of sqlite, but it may impact performance since it can end up updating the record twice (writing the original value then the new value if applicable).

另一个问题是，即使条件表明不应对其进行更新，最终还是要写入该表。

The other problem is that you end up with a write to the table even if the condition states that it should not be updated.

这篇关于SQLite插入和替换条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！