database SQLite UPSERT/更新或插入

Question

提问by bgusach

I need to perform UPSERT / INSERT OR UPDATE against a SQLite Database.

我需要对 SQLite 数据库执行 UPSERT/INSERT OR UPDATE。

There is the command INSERT OR REPLACE which in many cases can be useful. But if you want to keep your id's with autoincrement in place because of foreign keys, it does not work since it deletes the row, creates a new one and consequently this new row has a new ID.

有命令 INSERT OR REPLACE 在许多情况下可能很有用。但是，如果您想因为外键而保留带有自动增量的 id，则它不起作用，因为它删除了该行，创建了一个新行，因此该新行有一个新 ID。

This would be the table:

这将是表：

players - (primary key on id, user_name unique)

玩家 - （id 上的主键，user_name 唯一）

|  id   | user_name |  age   |
------------------------------
|  1982 |   johnny  |  23    |
|  1983 |   steven  |  29    |
|  1984 |   pepee   |  40    |

Answer 1

采纳答案by prapin

This is a late answer. Starting from SQLIte 3.24.0, released on June 4, 2018, there is finally a support for UPSERTclause following PostgreSQL syntax.

这是一个迟到的答案。从 2018 年 6 月 4 日发布的 SQLIte 3.24.0 开始，终于支持遵循 PostgreSQL 语法的UPSERT子句。

INSERT INTO players (user_name, age)
  VALUES('steven', 32) 
  ON CONFLICT(user_name) 
  DO UPDATE SET age=excluded.age;

Note: For those having to use a version of SQLite earlier than 3.24.0, please reference this answerbelow (posted by me, @MarqueIV).
However if you do have the option to upgrade, you are strongly encouragedto do so as unlike my solution, the one posted here achieves the desired behavior in a single statement. Plus you get all the other features, improvements and bug fixes that usually come with a more recent release.

注意：对于那些必须使用早于 3.24.0 版本的 SQLite，请参考下面的这个答案（由我发布，@MarqueIV）。
但是，如果您确实可以选择升级，则强烈建议您这样做，因为与我的解决方案不同，此处发布的解决方案在单个语句中实现了所需的行为。此外，您还可以获得更新版本通常附带的所有其他功能、改进和错误修复。

Answer 2

回答by bgusach

Q&A Style

问答风格

Well, after researching and fighting with the problem for hours, I found out that there are two ways to accomplish this, depending on the structure of your table and if you have foreign keys restrictions activated to maintain integrity. I'd like to share this in a clean format to save some time to the people that may be in my situation.

好吧，经过数小时的研究和解决问题后，我发现有两种方法可以实现这一点，具体取决于表的结构以及是否激活了外键限制以保持完整性。我想以一种干净的格式分享这个，以节省一些时间给可能处于我这种情况的人。

Option 1: You can afford deleting the row

选项 1：您可以负担得起删除该行

In other words, you don't have foreign key, or if you have them, your SQLite engine is configured so that there no are integrity exceptions. The way to go is INSERT OR REPLACE. If you are trying to insert/update a player whose ID already exists, the SQLite engine will delete that row and insert the data you are providing. Now the question comes: what to do to keep the old ID associated?

换句话说，您没有外键，或者如果您有外键，则您的 SQLite 引擎已配置为不存在完整性异常。要走的路是INSERT OR REPLACE。如果您尝试插入/更新 ID 已存在的玩家，SQLite 引擎将删除该行并插入您提供的数据。现在问题来了：如何保持旧 ID 关联？

Let's say we want to UPSERTwith the data user_name='steven' and age=32.

假设我们想要使用数据 user_name='steven' 和 age=32进行UPSERT。

Look at this code:

看看这段代码：

INSERT INTO players (id, name, age)

VALUES (
    coalesce((select id from players where user_name='steven'),
             (select max(id) from drawings) + 1),
    32)

The trick is in coalesce. It returns the id of the user 'steven' if any, and otherwise, it returns a new fresh id.

诀窍在于合并。如果有的话，它返回用户“steven”的 id，否则，它返回一个新的新 id。

Option 2: You cannot afford deleting the row

选项 2：您负担不起删除该行的费用

After monkeying around with the previous solution, I realized that in my case that could end up destroying data, since this ID works as a foreign key for other table. Besides, I created the table with the clause ON DELETE CASCADE, which would mean that it'd delete data silently. Dangerous.

在对之前的解决方案进行了反复琢磨之后，我意识到在我的情况下这可能最终会破坏数据，因为此 ID 可用作其他表的外键。此外，我使用ON DELETE CASCADE子句创建了表，这意味着它会以静默方式删除数据。危险的。

So, I first thought of a IF clause, but SQLite only has CASE. And this CASEcan't be used (or at least I did not manage it) to perform one UPDATEquery if EXISTS(select id from players where user_name='steven'), and INSERTif it didn't. No go.

所以，我首先想到了一个 IF 子句，但 SQLite 只有CASE。如果 EXISTS（select id from player where user_name='steven'），则不能使用此CASE（或者至少我没有管理它）执行一个UPDATE查询，如果没有，则不能使用INSERT。不行。

And then, finally I used the brute force, with success. The logic is, for each UPSERTthat you want to perform, first execute a INSERT OR IGNOREto make sure there is a row with our user, and then execute an UPDATEquery with exactly the same data you tried to insert.

然后，最后我使用了蛮力，成功了。逻辑是，对于您要执行的每个UPSERT，首先执行INSERT OR IGNORE以确保我们的用户有一行，然后使用与您尝试插入的数据完全相同的数据执行UPDATE查询。

Same data as before: user_name='steven' and age=32.

与之前相同的数据：user_name='steven' 和 age=32。

-- make sure it exists
INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32); 

-- make sure it has the right data
UPDATE players SET user_name='steven', age=32 WHERE user_name='steven';

And that's all!

就这样！

EDIT

编辑

As Andy has commented, trying to insert first and then update may lead to firing triggers more often than expected. This is not in my opinion a data safety issue, but it is true that firing unnecessary events makes little sense. Therefore, a improved solution would be:

正如安迪所评论的，尝试先插入然后更新可能会导致比预期更频繁地触发触发器。在我看来，这不是数据安全问题，但确实触发不必要的事件毫无意义。因此，改进的解决方案是：

-- Try to update any existing row
UPDATE players SET age=32 WHERE user_name='steven';

-- Make sure it exists
INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32);

Answer 3

回答by Mark A. Donohoe

Here's an approach that doesn't require the brute-force 'ignore' which would only work if there was a key violation. This way works based on anyconditions you specify in the update.

这是一种不需要蛮力“忽略”的方法，这种方法仅在存在密钥违规时才有效。这种方式基于您在更新中指定的任何条件。

Try this...

尝试这个...

-- Try to update any existing row
UPDATE players
SET age=32
WHERE user_name='steven';

-- If no update happened (i.e. the row didn't exist) then insert one
INSERT INTO players (user_name, age)
SELECT 'steven', 32
WHERE (Select Changes() = 0);

How It Works

这个怎么运作

The 'magic sauce' here is using Changes()in the Whereclause. Changes()represents the number of rows affected by the last operation, which in this case is the update.

在“魔汁”这里是使用Changes()中Where的条款。 Changes()表示受上次操作影响的行数，在本例中是更新。

In the above example, if there are no changes from the update (i.e. the record doesn't exist) then Changes()= 0 so the Whereclause in the Insertstatement evaluates to true and a new row is inserted with the specified data.

在上面的示例中，如果更新没有更改（即记录不存在），则Changes()= 0 则Where语句中的子句Insert评估为真，并插入带有指定数据的新行。

If the Updatedidupdate an existing row, then Changes()= 1 (or more accurately, not zero if more than one row was updated), so the 'Where' clause in the Insertnow evaluates to false and thus no insert will take place.

如果Update确实更新了现有行，则Changes()= 1（或更准确地说，如果更新了不止一行，则不为零），因此Insertnow 中的“Where”子句评估为 false，因此不会发生插入。

The beauty of this is there's no brute-force needed, nor unnecessarily deleting, then re-inserting data which may result in messing up downstream keys in foreign-key relationships.

这样做的好处是不需要蛮力，也不需要不必要地删除，然后重新插入数据，这可能会导致外键关系中的下游键混乱。

Additionally, since it's just a standard Whereclause, it can be based on anything you define, not just key violations. Likewise, you can use Changes()in combination with anything else you want/need anywhere expressions are allowed.

此外，由于它只是一个标准Where条款，它可以基于您定义的任何内容，而不仅仅是关键违规。同样，您可以Changes()在任何允许表达式的地方与您想要/需要的任何其他内容结合使用。

Answer 4

回答by Andy

The problem with all presented answers it complete lack of taking triggers (and probably other side effects) into account. Solution like

所有提出的答案的问题都完全没有考虑触发因素（可能还有其他副作用）。解决方案如

INSERT OR IGNORE ...
UPDATE ...

leads to both triggers executed (for insert and then for update) when row does not exist.

导致在行不存在时执行两个触发器（用于插入，然后用于更新）。

Proper solution is

正确的解决办法是

UPDATE OR IGNORE ...
INSERT OR IGNORE ...

in that case only one statement is executed (when row exists or not).

在这种情况下，只执行一个语句（当行存在与否时）。

Answer 5

回答by Gilco

To have a pure UPSERT with no holes (for programmers) that don't relay on unique and other keys:

要拥有一个没有孔（对于程序员）的纯 UPSERT，它不会在唯一键和其他键上进行中继：

UPDATE players SET user_name="gil", age=32 WHERE user_name='george'; 
SELECT changes();

SELECT changes() will return the number of updates done in the last inquire. Then check if return value from changes() is 0, if so execute:

SELECT changes() 将返回上次查询中完成的更新次数。然后检查changes()的返回值是否为0，如果是则执行：

INSERT INTO players (user_name, age) VALUES ('gil', 32);

Answer 6

回答by Maximilian Tyrtania

You can also just add an ON CONFLICT REPLACE clause to your user_name unique constraint and then just INSERT away, leaving it to SQLite to figure out what to do in case of a conflict. See:https://sqlite.org/lang_conflict.html.

你也可以在你的 user_name 唯一约束中添加一个 ON CONFLICT REPLACE 子句，然后直接插入，把它留给 SQLite 来确定在发生冲突时该怎么做。请参阅：https: //sqlite.org/lang_conflict.html。

Also note the sentence regarding delete triggers: When the REPLACE conflict resolution strategy deletes rows in order to satisfy a constraint, delete triggers fire if and only if recursive triggers are enabled.

另请注意有关删除触发器的句子：当 REPLACE 冲突解决策略删除行以满足约束时，删除触发器当且仅当启用递归触发器时触发。

Answer 7

回答by itsho

Option 1: Insert -> Update

选项 1：插入 -> 更新

If you like to avoid both changes()=0and INSERT OR IGNOREeven if you cannot afford deleting the row - You can use this logic;

如果您想避免两者changes()=0，INSERT OR IGNORE即使您负担不起删除该行 - 您可以使用此逻辑；

First, insert(if not exists) and then updateby filtering with the unique key.

首先，插入（如果不存在），然后通过使用唯一键过滤进行更新。

Example

例子

-- Table structure
CREATE TABLE players (
    id        INTEGER       PRIMARY KEY AUTOINCREMENT,
    user_name VARCHAR (255) NOT NULL
                            UNIQUE,
    age       INTEGER       NOT NULL
);

-- Insert if NOT exists
INSERT INTO players (user_name, age)
SELECT 'johnny', 20
WHERE NOT EXISTS (SELECT 1 FROM players WHERE user_name='johnny' AND age=20);

-- Update (will affect row, only if found)
-- no point to update user_name to 'johnny' since it's unique, and we filter by it as well
UPDATE players 
SET age=20 
WHERE user_name='johnny';

Regarding Triggers

关于触发器

Notice: I haven't tested it to see the which triggers are being called, but I assumethe following:

注意：我还没有测试它以查看正在调用哪些触发器，但我假设以下内容：

if row does not exists

如果行不存在

BEFORE INSERT
INSERT using INSTEAD OF
AFTER INSERT
BEFORE UPDATE
UPDATE using INSTEAD OF
AFTER UPDATE

插入前
使用 INSTEAD OF 插入
插入后
更新前
使用 INSTEAD OF 更新
更新后

if row does exists

如果行确实存在

BEFORE UPDATE
UPDATE using INSTEAD OF
AFTER UPDATE

更新前
使用 INSTEAD OF 更新
更新后

Option 2: Insert or replace - keep your own ID

选项 2：插入或替换 - 保留您自己的 ID

in this way you can have a single SQL command

通过这种方式，您可以拥有一个 SQL 命令

-- Table structure
CREATE TABLE players (
    id        INTEGER       PRIMARY KEY AUTOINCREMENT,
    user_name VARCHAR (255) NOT NULL
                            UNIQUE,
    age       INTEGER       NOT NULL
);

-- Single command to insert or update
INSERT OR REPLACE INTO players 
(id, user_name, age) 
VALUES ((SELECT id from players WHERE user_name='johnny' AND age=20),
        'johnny',
        20);

Edit: added option 2.

编辑：添加选项 2。

database SQLite UPSERT/更新或插入

提问by bgusach

采纳答案by prapin

回答by bgusach

Q&A Style

问答风格

Option 1: You can afford deleting the row

选项 1：您可以负担得起删除该行

Option 2: You cannot afford deleting the row

选项 2：您负担不起删除该行的费用

EDIT

编辑

回答by Mark A. Donohoe

How It Works

这个怎么运作

回答by Andy

回答by Gilco

回答by Maximilian Tyrtania

回答by itsho

Option 1: Insert -> Update

选项 1：插入 -> 更新

Example

例子

Regarding Triggers

关于触发器

if row does not exists

如果行不存在

if row does exists

如果行确实存在

Option 2: Insert or replace - keep your own ID

选项 2：插入或替换 - 保留您自己的 ID

相关推荐

最近更新

标签

database SQLite UPSERT/更新或插入

提问by bgusach

采纳答案by prapin

回答by bgusach

Q&A Style

问答风格

Option 1: You can afford deleting the row

选项 1：您可以负担得起删除该行

Option 2: You cannot afford deleting the row

选项 2：您负担不起删除该行的费用

EDIT

编辑

回答by Mark A. Donohoe

How It Works

这个怎么运作

回答by Andy

回答by Gilco

回答by Maximilian Tyrtania

回答by itsho

Option 1: Insert -> Update

选项 1：插入 -> 更新

Example

例子

Regarding Triggers

关于触发器

if row does not exists

如果行不存在

if row does exists

如果行确实存在

Option 2: Insert or replace - keep your own ID

选项 2：插入或替换 - 保留您自己的 ID

相关推荐

使用 VBA 声明相对于活动单元格的范围

database 如何将 mongodb 字符集设置为 utf8？

vba 过滤掉特定值

database 仅对表中的一列授予更改权限

相关推荐

最近更新

标签