SQL SQLite - UPSERT not INSERT 或 REPLACE

Question

提问by Mike Trader

http://en.wikipedia.org/wiki/Upsert

Is there some clever way to do this in SQLite that I have not thought of?

在 SQLite 中是否有一些我没有想到的聪明方法来做到这一点？

Basically I want to update three out of four columns if the record exists, If it does not exists I want to INSERT the record with the default (NUL) value for the fourth column.

基本上，如果记录存在，我想更新四列中的三列，如果它不存在，我想用第四列的默认 (NUL) 值插入记录。

The ID is a primary key so there will only ever be one record to UPSERT.

ID 是主键，因此 UPSERT 将永远只有一条记录。

(I am trying to avoid the overhead of SELECT in order to determin if I need to UPDATE or INSERT obviously)

（我试图避免 SELECT 的开销，以确定是否需要 UPDATE 或 INSERT 显然）

Suggestions?

建议？

I cannot confirm that Syntax on the SQLite site for TABLE CREATE. I have not built a demo to test it, but It doesnt seem to be supported..

我无法在 SQLite 站点上确认 TABLE CREATE 的语法。我还没有建立一个演示来测试它，但它似乎不受支持..

If it was, I have three columns so it would actually look like:

如果是，我有三列，所以它实际上看起来像：

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    Blob1 BLOB ON CONFLICT REPLACE, 
    Blob2 BLOB ON CONFLICT REPLACE, 
    Blob3 BLOB 
);

but the first two blobs will not cause a conflict, only the ID would So I asusme Blob1 and Blob2 would not be replaced (as desired)

但前两个 blob 不会引起冲突，只有 ID 会所以我假设 Blob1 和 Blob2 不会被替换（根据需要）

UPDATEs in SQLite when binding data are a complete transaction, meaning Each sent row to be updated requires: Prepare/Bind/Step/Finalize statements unlike the INSERT which allows the use of the reset function

当绑定数据是一个完整的事务时，SQLite 中的 UPDATE，这意味着要更新的每个发送的行都需要： Prepare/Bind/Step/Finalize 语句与允许使用重置功能的 INSERT 不同

The life of a statement object goes something like this:

语句对象的生命周期是这样的：

Create the object using sqlite3_prepare_v2()
Bind values to host parameters using sqlite3_bind_ interfaces.
Run the SQL by calling sqlite3_step()
Reset the statement using sqlite3_reset() then go back to step 2 and repeat.
Destroy the statement object using sqlite3_finalize().

使用 sqlite3_prepare_v2() 创建对象
使用 sqlite3_bind_ 接口将值绑定到主机参数。
通过调用 sqlite3_step() 运行 SQL
使用 sqlite3_reset() 重置语句，然后返回步骤 2 并重复。
使用 sqlite3_finalize() 销毁语句对象。

UPDATE I am guessing is slow compared to INSERT, but how does it compare to SELECT using the Primary key?

UPDATE 我猜与 INSERT 相比比较慢，但它与使用主键的 SELECT 相比如何？

Perhaps I should use the select to read the 4th column (Blob3) and then use REPLACE to write a new record blending the original 4th Column with the new data for the first 3 columns?

也许我应该使用 select 来读取第 4 列 (Blob3)，然后使用 REPLACE 编写一条新记录，将原始第 4 列与前 3 列的新数据混合？

Answer 1

回答by Eric B

Assuming three columns in the table: ID, NAME, ROLE

假设表中有三列：ID、NAME、ROLE

BAD:This will insert or replace all columns with new values for ID=1:

坏：这将插入或替换所有列的 ID=1 的新值：

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

BAD:This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:

坏：这将插入或替换 2 列...... NAME 列将被设置为 NULL 或默认值：

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

GOOD: Use SQLite On conflict clause UPSERT support in SQLite!UPSERT syntax was added to SQLite with version 3.24.0!

好：在 SQLite 中使用 SQLite On 冲突子句 UPSERT 支持！UPSERT 语法已添加到 SQLite 3.24.0 版！

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.

UPSERT 是对 INSERT 的特殊语法补充，如果 INSERT 违反唯一性约束，它会导致 INSERT 表现为 UPDATE 或无操作。UPSERT 不是标准 SQL。SQLite 中的 UPSERT 遵循 PostgreSQL 建立的语法。

GOOD but tendous:This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be the default (NULL).

好但有条理：这将更新 2 列。当 ID=1 存在时，NAME 将不受影响。当 ID=1 不存在时，名称将是默认值 (NULL)。

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.

这将更新 2 列。当 ID=1 存在时，ROLE 将不受影响。当 ID=1 不存在时，角色将被设置为“Benchwarmer”而不是默认值。

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

Answer 2

回答by gregschlom

INSERT OR REPLACE is NOTequivalent to "UPSERT".

INSERT OR REPLACE不等同于“UPSERT”。

Say I have the table Employee with the fields id, name, and role:

假设我有包含字段 id、name 和 role 的 Employee 表：

INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO")
INSERT OR REPLACE INTO Employee ("id", "role") VALUES (1, "code monkey")

Boom, you've lost the name of the employee number 1. SQLite has replaced it with a default value.

Boom，您丢失了员工编号 1 的姓名。SQLite 已将其替换为默认值。

The expected output of an UPSERT would be to change the role and to keep the name.

UPSERT 的预期输出是更改角色并保留名称。

Answer 3

回答by Aristotle Pagaltzis

Eric B's answeris OK if you want to preserve just one or maybe two columns from the existing row. If you want to preserve a lot of columns, it gets too cumbersome fast.

如果您只想保留现有行中的一列或两列，Eric B 的答案是可以的。如果你想保留很多列，它会变得太麻烦。

Here's an approach that will scale well to any amount of columns on either side. To illustrate it I will assume the following schema:

这是一种可以很好地扩展到任意数量列的方法。为了说明它，我将假设以下模式：

 CREATE TABLE page (
     id      INTEGER PRIMARY KEY,
     name    TEXT UNIQUE,
     title   TEXT,
     content TEXT,
     author  INTEGER NOT NULL REFERENCES user (id),
     ts      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );

Note in particular that nameis the natural key of the row – idis used only for foreign keys, so the point is for SQLite to pick the ID value itself when inserting a new row. But when updating an existing row based on its name, I want it to continue to have the old ID value (obviously!).

特别要注意的是，这name是行的自然键——id仅用于外键，所以重点是 SQLite 在插入新行时选择 ID 值本身。但是当基于它更新现有行时name，我希望它继续拥有旧的 ID 值（显然！）。

I achieve a true UPSERTwith the following construct:

我UPSERT通过以下构造实现了真实：

 WITH new (name, title, author) AS ( VALUES('about', 'About this site', 42) )
 INSERT OR REPLACE INTO page (id, name, title, content, author)
 SELECT old.id, new.name, new.title, old.content, new.author
 FROM new LEFT JOIN page AS old ON new.name = old.name;

The exact form of this query can vary a bit. The key is the use of INSERT SELECTwith a left outer join, to join an existing row to the new values.

此查询的确切形式可能略有不同。关键是使用INSERT SELECT左外连接，将现有行连接到新值。

Here, if a row did not previously exist, old.idwill be NULLand SQLite will then assign an ID automatically, but if there already was such a row, old.idwill have an actual value and this will be reused. Which is exactly what I wanted.

在这里，如果行以前不存在，old.id会NULL再SQLite的会自动分配一个ID，但如果已经有了这样的行，old.id将有一个实际值，这将被重用。这正是我想要的。

In fact this is very flexible. Note how the tscolumn is completely missing on all sides – because it has a DEFAULTvalue, SQLite will just do the right thing in any case, so I don't have to take care of it myself.

事实上，这是非常灵活的。请注意该ts列是如何从各个方面完全丢失的——因为它有一个DEFAULT值，SQLite 在任何情况下都会做正确的事情，所以我不必自己处理它。

You can also include a column on both the newand oldsides and then use e.g. COALESCE(new.content, old.content)in the outer SELECTto say “insert the new content if there was any, otherwise keep the old content” – e.g. if you are using a fixed query and are binding the new values with placeholders.

您还可以同时在一列new和old面，然后用如COALESCE(new.content, old.content)在外SELECT说“插入如果有任何新的内容，否则保留旧的内容” -例如，如果您使用的是固定的查询，并结合新的带占位符的值。

Answer 4

回答by Sam Saffron

If you are generally doing updates I would ..

如果您通常进行更新，我会..

Begin a transaction
Do the update
Check the rowcount
If it is 0 do the insert
Commit

开始交易
做更新
检查行数
如果它是 0 做插入
犯罪

If you are generally doing inserts I would

如果你通常做插入，我会

Begin a transaction
Try an insert
Check for primary key violation error
if we got an error do the update
Commit

开始交易
尝试插入
检查主键违规错误
如果我们遇到错误，请进行更新
犯罪

This way you avoid the select and you are transactionally sound on Sqlite.

通过这种方式，您可以避免选择，并且您在 Sqlite 上的事务性良好。

Answer 5

回答by AnthonyLambert

This answer has be updated and so the comments below no longer apply.

此答案已更新，因此以下评论不再适用。

2018-05-18 STOP PRESS.

2018-05-18 停止新闻。

UPSERT support in SQLite!UPSERT syntax was added to SQLite with version 3.24.0 (pending) !

SQLite 中的 UPSERT 支持！UPSERT 语法已添加到 SQLite 版本 3.24.0（待定）！

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.

UPSERT 是对 INSERT 的特殊语法补充，如果 INSERT 违反唯一性约束，它会导致 INSERT 表现为 UPDATE 或无操作。UPSERT 不是标准 SQL。SQLite 中的 UPSERT 遵循 PostgreSQL 建立的语法。

alternatively:

或者：

Another completely different way of doing this is: In my application I set my in memory rowID to be long.MaxValue when I create the row in memory. (MaxValue will never be used as an ID you will won't live long enough.... Then if rowID is not that value then it must already be in the database so needs an UPDATE if it is MaxValue then it needs an insert. This is only useful if you can track the rowIDs in your app.

另一种完全不同的方法是：在我的应用程序中，当我在内存中创建行时，我将内存中的 rowID 设置为 long.MaxValue。（MaxValue 永远不会被用作一个 ID，你不会活得足够长......然后如果 rowID 不是那个值那么它必须已经在数据库中所以需要一个 UPDATE 如果它是 MaxValue 那么它需要一个插入。这仅在您可以跟踪应用程序中的 rowID 时有用。

Answer 6

回答by Chris Stavropoulos

I realize this is an old thread but I've been working in sqlite3 as of late and came up with this method which better suited my needs of dynamically generating parameterized queries:

我意识到这是一个旧线程，但我最近一直在使用 sqlite3 并提出了这种方法，它更适合我动态生成参数化查询的需求：

insert or ignore into <table>(<primaryKey>, <column1>, <column2>, ...) values(<primaryKeyValue>, <value1>, <value2>, ...); 
update <table> set <column1>=<value1>, <column2>=<value2>, ... where changes()=0 and <primaryKey>=<primaryKeyValue>;

It's still 2 queries with a where clause on the update but seems to do the trick. I also have this vision in my head that sqlite can optimize away the update statement entirely if the call to changes() is greater than zero. Whether or not it actually does that is beyond my knowledge, but a man can dream can't he? ;)

它仍然是 2 个带有更新的 where 子句的查询，但似乎可以解决问题。我也有这样的想法，如果对 changes() 的调用大于零，sqlite 可以完全优化更新语句。它是否真的做到了这超出了我的知识范围，但一个人可以做梦，不是吗？;)

For bonus points you can append this line which returns you the id of the row whether it be a newly inserted row or an existing row.

对于奖励积分，您可以附加此行，它会返回行的 id，无论是新插入的行还是现有行。

select case changes() WHEN 0 THEN last_insert_rowid() else <primaryKeyValue> end;

Answer 7

回答by David Liebeherr

Here is a solution that really is an UPSERT (UPDATE or INSERT) instead of an INSERT OR REPLACE (which works differently in many situations).

这是一个真正是 UPSERT（UPDATE 或 INSERT）而不是 INSERT OR REPLACE（在许多情况下的工作方式不同）的解决方案。

It works like this:
1. Try to update if a record with the same Id exists.
2. If the update did not change any rows (NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0)), then insert the record.

它的工作原理是这样的：
1. 如果存在具有相同 Id 的记录，则尝试更新。
2. 如果更新没有更改任何行 ( NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0))，则插入记录。

So either an existing record was updated or an insert will be performed.

因此，要么更新现有记录，要么执行插入操作。

The important detail is to use the changes() SQL function to check if the update statement hit any existing records and only perform the insert statement if it did not hit any record.

重要的细节是使用 changes() SQL 函数来检查更新语句是否命中任何现有记录，如果没有命中任何记录，则仅执行插入语句。

One thing to mention is that the changes() function does not return changes performed by lower-level triggers (see http://sqlite.org/lang_corefunc.html#changes), so be sure to take that into account.

需要提及的一件事是，changes() 函数不会返回由较低级别触发器执行的更改（请参阅http://sqlite.org/lang_corefunc.html#changes），因此请务必考虑到这一点。

Here is the SQL...

这是SQL...

Test update:

测试更新：

--Create sample table and records (and drop the table if it already exists)
DROP TABLE IF EXISTS Contact;
CREATE TABLE [Contact] (
  [Id] INTEGER PRIMARY KEY, 
  [Name] TEXT
);
INSERT INTO Contact (Id, Name) VALUES (1, 'Mike');
INSERT INTO Contact (Id, Name) VALUES (2, 'John');

-- Try to update an existing record
UPDATE Contact
SET Name = 'Bob'
WHERE Id = 2;

-- If no record was changed by the update (meaning no record with the same Id existed), insert the record
INSERT INTO Contact (Id, Name)
SELECT 2, 'Bob'
WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0);

--See the result
SELECT * FROM Contact;

Test insert:

测试插入：

--Create sample table and records (and drop the table if it already exists)
DROP TABLE IF EXISTS Contact;
CREATE TABLE [Contact] (
  [Id] INTEGER PRIMARY KEY, 
  [Name] TEXT
);
INSERT INTO Contact (Id, Name) VALUES (1, 'Mike');
INSERT INTO Contact (Id, Name) VALUES (2, 'John');

-- Try to update an existing record
UPDATE Contact
SET Name = 'Bob'
WHERE Id = 3;

-- If no record was changed by the update (meaning no record with the same Id existed), insert the record
INSERT INTO Contact (Id, Name)
SELECT 3, 'Bob'
WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0);

--See the result
SELECT * FROM Contact;

Answer 8

回答by Lukasz Szozda

Beginning with version 3.24.0 UPSERT is supported by SQLite.

从 3.24.0 版开始，SQLite 支持 UPSERT。

From the documentation:

从文档：

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.UPSERT syntax was added to SQLite with version 3.24.0 (pending).
An UPSERT is an ordinary INSERT statement that is followed by the special ON CONFLICT clause

UPSERT 是对 INSERT 的特殊语法补充，如果 INSERT 违反唯一性约束，它会导致 INSERT 表现为 UPDATE 或无操作。UPSERT 不是标准 SQL。SQLite 中的 UPSERT 遵循 PostgreSQL 建立的语法。UPSERT 语法已添加到 SQLite 版本 3.24.0（待定）。
UPSERT 是一个普通的 INSERT 语句，后跟特殊的 ON CONFLICT 子句

^{Image source: https://www.sqlite.org/images/syntax/upsert-clause.gif}

^{图片来源：https: //www.sqlite.org/images/syntax/upsert-clause.gif}

Answer 9

回答by Brill Pappin

You can indeed do an upsert in SQLite, it just looks a little different than you are used to. It would look something like:

您确实可以在 SQLite 中执行 upsert，它只是看起来与您习惯的有点不同。它看起来像：

INSERT INTO table name (column1, column2) 
VALUES ("value12", "value2") WHERE id = 123 
ON CONFLICT DO UPDATE 
SET column1 = "value1", column2 = "value2" WHERE id = 123

Answer 10

回答by Brill Pappin

Expanding on Aristotle's answeryou can SELECT from a dummy 'singleton' table (a table of your own creation with a single row). This avoids some duplication.

扩展亚里士多德的答案，您可以从虚拟的“单例”表（您自己创建的单行表）中进行选择。这避免了一些重复。

I've also kept the example portable across MySQL and SQLite and used a 'date_added' column as an example of how you could set a column only the first time.

我还保留了该示例在 MySQL 和 SQLite 之间的可移植性，并使用了“date_ added”列作为如何仅在第一次设置列的示例。

 REPLACE INTO page (
   id,
   name,
   title,
   content,
   author,
   date_added)
 SELECT
   old.id,
   "about",
   "About this site",
   old.content,
   42,
   IFNULL(old.date_added,"21/05/2013")
 FROM singleton
 LEFT JOIN page AS old ON old.name = "about";

SQL SQLite - UPSERT not INSERT 或 REPLACE

提问by Mike Trader

回答by Eric B

回答by gregschlom

回答by Aristotle Pagaltzis

回答by Sam Saffron

回答by AnthonyLambert

回答by Chris Stavropoulos

回答by David Liebeherr

回答by Lukasz Szozda

Beginning with version 3.24.0 UPSERT is supported by SQLite.

从 3.24.0 版开始，SQLite 支持 UPSERT。

回答by Brill Pappin

回答by Brill Pappin

相关推荐

最近更新

标签

SQL SQLite - UPSERT *not* INSERT 或 REPLACE

提问by Mike Trader

回答by Eric B

回答by gregschlom

回答by Aristotle Pagaltzis

回答by Sam Saffron

回答by AnthonyLambert

回答by Chris Stavropoulos

回答by David Liebeherr

回答by Lukasz Szozda

Beginning with version 3.24.0 UPSERT is supported by SQLite.

从 3.24.0 版开始，SQLite 支持 UPSERT。

回答by Brill Pappin

回答by Brill Pappin

相关推荐

SQL PostgreSQL 列名是否区分大小写？

SQL 如何使用纯SQL选择N个随机行？

使用 SQL 查询识别趋势

按特定字段值排序 SQL 查询

相关推荐

最近更新

标签

SQL SQLite - UPSERT not INSERT 或 REPLACE