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插件的使用,请参考官方链接

其他回答

插入或替换不等同于“UPSERT”。

假设我有一个包含id、name和role字段的Employee表:

INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO")
INSERT OR REPLACE INTO Employee ("id", "role") VALUES (1, "code monkey")

砰,你把1号员工的名字弄丢了。SQLite已将其替换为默认值。

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

如果你通常做更新,我会..

开始事务 进行更新 检查行数 如果是0,则插入 提交

如果你通常做插入,我会

开始事务 尝试插入 检查主键违反错误 如果出现错误,就进行更新 提交

这样你就避免了选择,你在Sqlite上的事务是健全的。

假设表中有三列: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')
          );

扩展亚里士多德的答案,你可以从一个虚拟的“单行”表(你自己创建的单行表)中选择。这避免了一些重复。

我还保留了示例在MySQL和SQLite之间的可移植性,并使用“date_added”列作为如何仅第一次设置列的示例。

 REPLACE INTO page (
   id,
   name,
   title,
   content,
   author,
   date_added)
 SELECT
   old.id,
   "about",
   "About this site",
   old.content,
   42,
   IFNULL(old.date_added,"21/05/2013")
 FROM singleton
 LEFT JOIN page AS old ON old.name = "about";

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