SQLite 中的 UPSERT [英] UPSERT in SQLite

问题描述

我不想使用 REPLACE INTO 因为它基本上是一个 DELETE 和 INSERT 并且使用旧的数据很复杂列.

I don't want to use REPLACE INTO because it's basically a DELETE and INSERT and it's complicated to use the data from the old columns.

INSERT OR IGNORE 有点麻烦，因为所有错误都被忽略了，所以这不是一个选项.

INSERT OR IGNORE is a bit of a hack because all errors are ignored, so this is not an option.

我读过 博客文章 使用以下内容:

I've read a blog article which uses the following:

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)

我非常喜欢这种方法，但我不知道如何在 SQLite 中使用 @@ROWCOUNT 来实现这个 IF-clause，这就是我得到:

I like this approach really much, but I don't know how I can implement this IF-clause with the @@ROWCOUNT in SQLite, this is what I got:

SELECT CASE (SELECT
    CASE
        WHEN EXISTS(SELECT 1 FROM t WHERE id=2)
            THEN 1
            ELSE 0
    END)
WHEN 1
  UPDATE t set a='pdf' WHERE id=2;
ELSE
  INSERT INTO t (a) VALUES ('pdf');
END

SELECT CASE 似乎是在 SQLite 中使用 CASE-clause 的唯一方法，因为其他一切都会引发语法错误.但是也不可能在 SELECT CASE 中使用 UPDATE- 或 INSERT- 语句，因此这会引发错误.

the SELECT CASE seems to be the only way to use a CASE-clause in SQLite because everything else throws an syntax error. But it's also not possible to use a UPDATE- or INSERT-statement in the SELECT CASE, so this throws an error.

我已经尝试了以下

UPDATE t set a='pdf' WHERE id=2;
CASE WHEN (changes()=0) THEN
    INSERT INTO t (a) VALUES ('pdf');
END

但这不起作用，因为 CASE-clause 会引发语法错误.

but this doesn't work, because the CASE-clause throws an syntax error.

有人可以提供一个在 SQLite 中使用 @@ROWCOUNT 进行 UPSERT 的示例吗?

can someone provide an example using @@ROWCOUNT for an UPSERT in SQLite?

推荐答案

SQLite 没有内置的类似 UPSERT 的语句，不会删除旧记录.

SQLite has no built-in UPSERT-like statement that doesn't delete the old record.

您应该检查程序中的更改次数，并有条件地执行 INSERT.

You should check the number of changes in your program, and execute the INSERT conditionally.

然而，如果你真的想在 SQL 中做到这一点，这是可能的；但是您必须使用 INSERT ... SELECT ... 形式，以便您能够在需要时插入零条记录:

However, if you really want to do this in SQL, it's possible; but you have to use the INSERT ... SELECT ... form so that you are able to insert zero records, if needed:

BEGIN;
UPDATE t SET a = 'pdf' WHERE id = 2;
INSERT INTO t(id, a) SELECT 2, 'pdf' WHERE changes() = 0;
COMMIT;

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