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

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

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

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

ID是一个主键,所以UPSERT只会有一条记录。

(我试图避免选择的开销,以确定我是否需要更新或插入明显)

建议吗?


我无法确认在SQLite网站上创建表的语法。 我还没有构建一个演示来测试它,但它似乎不受支持。

如果是的话,我有三列,所以它实际上是这样的:

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

但前两个斑点不会引起冲突,只有ID会 所以我假设Blob1和Blob2不会被替换(如期望的那样)


SQLite中绑定数据时的更新是一个完整的事务,也就是说 每个要更新的发送行都需要:Prepare/Bind/Step/Finalize语句 与允许使用重置函数的INSERT不同

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

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

更新我猜是慢与插入相比,但它如何比较选择使用主键?

也许我应该使用选择读取第4列(Blob3),然后使用REPLACE写一个新记录混合原始的第4列与前3列的新数据?


当前回答

SELECT COUNT(*) FROM table1 WHERE id = 1;

如果COUNT(*) = 0

INSERT INTO table1(col1, col2, cole) VALUES(var1,var2,var3);

else if COUNT(*) > 0

UPDATE table1 SET col1 = var4, col2 = var5, col3 = var6 WHERE id = 1;

其他回答

从3.24.0版本开始,SQLite支持UPSERT。

从文档中可以看到:

UPSERT是INSERT的一个特殊语法,如果INSERT违反唯一性约束,它会导致INSERT行为为UPDATE或no-op。UPSERT不是标准SQL。SQLite中的UPSERT遵循PostgreSQL建立的语法。UPSERT语法被添加到3.24.0版本的SQLite中(待定)。 UPSERT是一个普通的INSERT语句,后面跟着特殊的ON冲突子句

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


例子:

CREATE TABLE t1(id INT PRIMARY KEY, c TEXT);
INSERT INTO t1(id, c) VALUES (1,'a'), (2, 'b');
SELECT * FROM t1;


INSERT INTO t1(id, c) VALUES (1, 'c');
-- UNIQUE constraint failed: t1.id

INSERT INTO t1(id, c) VALUES (1, 'c')
ON CONFLICT DO NOTHING;

SELECT * FROM t1;

INSERT INTO t1(id, c)
VALUES (1, 'c')
ON CONFLICT(id) DO UPDATE SET c = excluded.c;

SELECT * FROM t1;

db < > fiddle演示

SELECT COUNT(*) FROM table1 WHERE id = 1;

如果COUNT(*) = 0

INSERT INTO table1(col1, col2, cole) VALUES(var1,var2,var3);

else if COUNT(*) > 0

UPDATE table1 SET col1 = var4, col2 = var5, col3 = var6 WHERE id = 1;

我想这可能就是你想要的:ON冲突条款。

如果你这样定义你的表:

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    field1 TEXT 
); 

现在,如果您使用已经存在的id执行INSERT操作,SQLite将自动执行UPDATE而不是INSERT操作。

Hth……

我意识到这是一个旧线程,但我最近一直在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>; 

它仍然是2个查询,在更新上有一个where子句,但似乎做到了这一点。我还在脑海中设想,如果对changes()的调用大于零,sqlite可以完全优化掉更新语句。我不知道它是否真的做到了,但一个人可以梦想,不是吗?;)

为了获得额外的分数,您可以追加这一行,它将返回行id,无论它是新插入的行还是现有的行。

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

我所知道的最佳方法是先进行更新,然后进行插入。 “选择的开销”是必要的,但这并不是一个可怕的负担,因为您是在快速的主键上搜索。

你应该能够用你的表和字段名修改下面的语句来做你想做的事情。

--first, update any matches
UPDATE DESTINATION_TABLE DT
SET
  MY_FIELD1 = (
              SELECT MY_FIELD1
              FROM SOURCE_TABLE ST
              WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY
              )
 ,MY_FIELD2 = (
              SELECT MY_FIELD2
              FROM SOURCE_TABLE ST
              WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY
              )
WHERE EXISTS(
            SELECT ST2.PRIMARY_KEY
            FROM
              SOURCE_TABLE ST2
             ,DESTINATION_TABLE DT2
            WHERE ST2.PRIMARY_KEY = DT2.PRIMARY_KEY
            );

--second, insert any non-matches
INSERT INTO DESTINATION_TABLE(
  MY_FIELD1
 ,MY_FIELD2
)
SELECT
  ST.MY_FIELD1
 ,NULL AS MY_FIELD2  --insert NULL into this field
FROM
  SOURCE_TABLE ST
WHERE NOT EXISTS(
                SELECT DT2.PRIMARY_KEY
                FROM DESTINATION_TABLE DT2
                WHERE DT2.PRIMARY_KEY = ST.PRIMARY_KEY
                );