替代 SQLite INSERT ... ON CONFLICT ... WHERE ... DO UPDATE SET [英] Alternative to SQLite INSERT ... ON CONFLICT ... WHERE ... DO UPDATE SET

问题描述

我正在 Linux 上运行一个使用 SQLite3 版本 3.7.17 的应用程序.这条语句出错了:

I'm running an application that uses SQLite3 version 3.7.17 on Linux. It's erroring out on this statement:

INSERT INTO taxa (taxon_id, rank, parent_id) VALUES (?,?,?)
        ON CONFLICT (taxon_id) WHERE parent_id is NULL
        DO UPDATE SET parent_id=excluded.parent_id,rank=excluded.rank

但同样的代码在 3.28.0 版本上运行.是否有另一种编写此语句的方法，以便它可以在 3.7.17 上运行?

But the same code runs on version 3.28.0. Is there another way of writing this statement so it can run on 3.7.17?

推荐答案

ON CONFLICT... 或 UPSERT 已在 3.24.0 版中添加到 SQLite.

ON CONFLICT... or UPSERT was added to SQLite in version 3.24.0.

在早期版本中，您可以通过 2 个单独的语句获得类似的功能.

In earlier versions you can get similar functionality with 2 separate statements.

首先尝试更新表:

UPDATE taxa 
SET rank = ?, parent_id = ?
WHERE taxon_id = ?;

如果存在 taxon_id = ? 的行，它将被更新.
如果它不存在，什么都不会发生.

If a row with the taxon_id = ? exists it will be updated.
If it does not exist nothing will happen.

然后尝试使用 INSERT OR IGNORE 插入新行:

Then try to insert the new row with INSERT OR IGNORE:

INSERT OR IGNORE INTO taxa (taxon_id, rank, parent_id) VALUES (?, ?, ?);

如果存在 taxon_id = ? 的行，则不会发生任何事情(我假设 taxon_id 是表的 PRIMARY KEY 或在至少定义为 UNIQUE).
如果它不存在，则将插入新行.

If a row with the taxon_id = ? exists nothing will happen (I assume that taxon_id is the PRIMARY KEY of the table or at least defined as UNIQUE).
If it does not exist then the new row will be inserted.

这篇关于替代 SQLite INSERT ... ON CONFLICT ... WHERE ... DO UPDATE SET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！