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列的新数据?
如果有人想阅读我在科尔多瓦的SQLite解决方案，我得到了这个通用的js方法，感谢@david的回答上面。
function addOrUpdateRecords(tableName, values, callback) {
get_columnNames(tableName, function (data) {
var columnNames = data;
myDb.transaction(function (transaction) {
var query_update = "";
var query_insert = "";
var update_string = "UPDATE " + tableName + " SET ";
var insert_string = "INSERT INTO " + tableName + " SELECT ";
myDb.transaction(function (transaction) {
// Data from the array [[data1, ... datan],[()],[()]...]:
$.each(values, function (index1, value1) {
var sel_str = "";
var upd_str = "";
var remoteid = "";
$.each(value1, function (index2, value2) {
if (index2 == 0) remoteid = value2;
upd_str = upd_str + columnNames[index2] + "='" + value2 + "', ";
sel_str = sel_str + "'" + value2 + "', ";
});
sel_str = sel_str.substr(0, sel_str.length - 2);
sel_str = sel_str + " WHERE NOT EXISTS(SELECT changes() AS change FROM "+tableName+" WHERE change <> 0);";
upd_str = upd_str.substr(0, upd_str.length - 2);
upd_str = upd_str + " WHERE remoteid = '" + remoteid + "';";
query_update = update_string + upd_str;
query_insert = insert_string + sel_str;
// Start transaction:
transaction.executeSql(query_update);
transaction.executeSql(query_insert);
});
}, function (error) {
callback("Error: " + error);
}, function () {
callback("Success");
});
});
});
}
因此，首先用这个函数获取列名:
function get_columnNames(tableName, callback) {
myDb.transaction(function (transaction) {
var query_exec = "SELECT name, sql FROM sqlite_master WHERE type='table' AND name ='" + tableName + "'";
transaction.executeSql(query_exec, [], function (tx, results) {
var columnParts = results.rows.item(0).sql.replace(/^[^\(]+\(([^\)]+)\)/g, '$1').split(','); ///// RegEx
var columnNames = [];
for (i in columnParts) {
if (typeof columnParts[i] === 'string')
columnNames.push(columnParts[i].split(" ")[0]);
};
callback(columnNames);
});
});
}
然后以编程方式构建事务。
“Values”是您应该在之前构建的数组，它表示您想要插入或更新到表中的行。
“remoteid”是我用作参考的id，因为我正在与远程服务器同步。
关于SQLite Cordova插件的使用，请参考官方链接
假设表中有三列:ID、NAME、ROLE
坏:这将插入或替换所有列为ID=1的新值:
INSERT OR REPLACE INTO Employee (id, name, role)
VALUES (1, 'John Foo', 'CEO');
坏:这将插入或替换2列…NAME列将被设置为NULL或默认值:
INSERT OR REPLACE INTO Employee (id, role)
VALUES (1, 'code monkey');
好:在冲突子句上使用SQLite
在SQLite中支持UPSERT !UPSERT语法在3.24.0版本添加到SQLite !
UPSERT是INSERT的一个特殊语法，如果INSERT违反唯一性约束，它会导致INSERT行为为UPDATE或no-op。UPSERT不是标准SQL。SQLite中的UPSERT遵循PostgreSQL建立的语法。
好但乏味:这将更新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')
);
我所知道的最佳方法是先进行更新，然后进行插入。
“选择的开销”是必要的，但这并不是一个可怕的负担，因为您是在快速的主键上搜索。
你应该能够用你的表和字段名修改下面的语句来做你想做的事情。
--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
);
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值本身。但是，当根据现有行的名称更新该行时，我希望它继续拥有旧的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列是如何完全缺失的-因为它有一个默认值，SQLite在任何情况下都会做正确的事情，所以我不必自己照顾它。
你也可以在新侧和旧侧同时包含一个列，然后使用例如COALESCE(new。content, old.content)在外层的SELECT语句中表示“如果有新内容，则插入新内容，否则保留旧内容”——例如，如果您正在使用固定查询并将新值与占位符绑定。
使用WHERE选择更新日期记录的upserting完整示例。
-- https://www.db-fiddle.com/f/7jyj4n76MZHLLk2yszB6XD/22
DROP TABLE IF EXISTS db;
CREATE TABLE db
(
id PRIMARY KEY,
updated_at,
other
);
-- initial INSERT
INSERT INTO db (id,updated_at,other) VALUES(1,1,1);
SELECT * FROM db;
-- INSERT without WHERE
INSERT INTO db (id,updated_at,other) VALUES(1,2,2)
ON CONFLICT(id) DO UPDATE SET updated_at=excluded.updated_at;
SELECT * FROM db;
-- WHERE is FALSE
INSERT INTO db (id,updated_at,other) VALUES(1,2,3)
ON CONFLICT(id) DO UPDATE SET updated_at=excluded.updated_at, other=excluded.other
WHERE excluded.updated_at > updated_at;
SELECT * FROM db;
-- ok to SET a PRIMARY KEY. WHERE is TRUE
INSERT INTO db (id,updated_at,other) VALUES(1,3,4)
ON CONFLICT(id) DO UPDATE SET id=excluded.id, updated_at=excluded.updated_at, other=excluded.other
WHERE excluded.updated_at > updated_at;
SELECT * FROM db;
