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列的新数据?
从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演示
这里有一个解决方案，它实际上是UPSERT (UPDATE或INSERT)，而不是INSERT或REPLACE(在许多情况下工作方式不同)。
它是这样工作的:
1. 如果存在具有相同Id的记录，请尝试更新。
2. 如果更新没有改变任何行(not EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0))，则插入该记录。
因此，要么更新现有记录，要么执行插入。
重要的细节是使用changes() SQL函数来检查更新语句是否命中任何现有记录，如果没有命中任何记录，则只执行插入语句。
需要提到的一点是changes()函数不会返回低级触发器执行的更改(请参阅http://sqlite.org/lang_corefunc.html#changes)，因此一定要考虑到这一点。
下面是SQL…
测试更新:
--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;
测试插入:
--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;
我意识到这是一个旧线程，但我最近一直在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
);
遵循Aristotle Pagaltzis和Eric B回答中的COALESCE思想，这里是一个upsert选项，用于仅更新少数列或在不存在时插入整行。
在这种情况下，想象一下标题和内容应该被更新，当存在时保持其他旧值，当没有找到name时插入提供的值:
说明id在INSERT时被强制为NULL，因为它应该是自动递增的。如果它只是一个生成的主键，那么也可以使用COALESCE(参见Aristotle Pagaltzis的注释)。
WITH new (id, name, title, content, author)
AS ( VALUES(100, 'about', 'About this site', 'Whatever new content here', 42) )
INSERT OR REPLACE INTO page (id, name, title, content, author)
SELECT
old.id, COALESCE(old.name, new.name),
new.title, new.content,
COALESCE(old.author, new.author)
FROM new LEFT JOIN page AS old ON new.name = old.name;
一般规则是，如果你想保留旧值，使用COALESCE，当你想更新值时，使用new。fieldname
