UPSERT 不是 INSERT或REPLACE

2018-05-30 03:04:15

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

在SQL Server上插入更新存储过程

在SQLite中有没有想到的一些聪明的方法？

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

该ID是一个主键，因此只有一个记录要输入到UPSERT中。

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

建议？

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

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

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

但前两个blob不会导致冲突，只有ID会因此我的asusme Blob1和Blob2不会被替换（根据需要）

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

声明对象的生命是这样的：

使用sqlite3_prepare_v2（）创建对象

使用sqlite3_bind_接口将值绑定到主机参数。

通过调用sqlite3_step（）来运行SQL

使用sqlite3_reset（）重置语句，然后返回到步骤2并重复。

使用sqlite3_finalize（）销毁语句对象。

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

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

假设表中有3列.. ID，NAME，ROLE

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

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

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

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

好的：这将更新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)
          );

这将更新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')
          );

INSERT或REPLACE不等于“UPSERT”。

假设我的表Employee具有字段id，名称和角色：

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，你已经失去了第一号员工的名字.SQLite已经用默认值取代了它。

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

如果您想保留现有行中的一列或两列，Eric B的答案是确定的。如果你想保存很多列，它会变得非常麻烦。

这种方法可以很好地适应任何一边的任意数量的列。为了说明这一点，我将假定以下模式：

 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
 );

特别注意， name是行的自然键 - id仅用于外键，因此当插入新行时SQLite会自己选择ID值。但是，当基于name更新现有行时，我希望它继续具有旧的ID值（显然！）。

我用以下构造实现了一个真正的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;

这个查询的确切形式可能会有所不同。关键是使用带有左外连接的INSERT SELECT来将现有行连接到新值。

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

实际上这非常灵活。注意ts列在所有方面都完全缺失 - 因为它有一个DEFAULT值，SQLite在任何情况下都会做正确的事情，所以我不必亲自处理它。

您还可以包括在两列new和old两侧，然后用如COALESCE(new.content, old.content)外SELECT说：“如果有任何插入新的内容，否则保留旧的内容” -例如，如果您正在使用固定查询并将新值绑定到占位符。

链接地址: http://www.djcxy.com/p/3335.html

上一篇: UPSERT *not* INSERT or REPLACE

下一篇: How to list the tables in an SQLite database file that was opened with ATTACH?

UPSERT *不是* INSERT或REPLACE

UPSERT 不是 INSERT或REPLACE