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列的新数据?
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语句中表示“如果有新内容，则插入新内容，否则保留旧内容”——例如，如果您正在使用固定查询并将新值与占位符绑定。
如果有人想阅读我在科尔多瓦的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插件的使用，请参考官方链接
我意识到这是一个旧线程，但我最近一直在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;
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语句中表示“如果有新内容，则插入新内容，否则保留旧内容”——例如，如果您正在使用固定查询并将新值与占位符绑定。
这种方法混合了回答这个问题的一些其他方法，并结合了CTE(公共表表达式)的使用。我将介绍这个查询，然后解释我为什么这么做。
如果有员工300的话，我想把员工300的姓改成DAVIS。否则，我将增加一个新员工。
表名:员工
列:id、first_name、last_name
查询为:
INSERT OR REPLACE INTO employees (employee_id, first_name, last_name)
WITH registered_employees AS ( --CTE for checking if the row exists or not
SELECT --this is needed to ensure that the null row comes second
*
FROM (
SELECT --an existing row
*
FROM
employees
WHERE
employee_id = '300'
UNION
SELECT --a dummy row if the original cannot be found
NULL AS employee_id,
NULL AS first_name,
NULL AS last_name
)
ORDER BY
employee_id IS NULL --we want nulls to be last
LIMIT 1 --we only want one row from this statement
)
SELECT --this is where you provide defaults for what you would like to insert
registered_employees.employee_id, --if this is null the SQLite default will be used
COALESCE(registered_employees.first_name, 'SALLY'),
'DAVIS'
FROM
registered_employees
;
基本上，我使用CTE来减少必须使用select语句来确定默认值的次数。因为这是一个CTE，所以我们只需要从表中选择我们想要的列，INSERT语句就使用了这一点。
现在可以通过将COALESCE函数中的空值替换为值来决定要使用的缺省值。
