SQLite - UPSERT不插入或替换

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

2017-01-31 15:57:00

其他回答

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

2010-12-02 00:55:15

如果有人想阅读我在科尔多瓦的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插件的使用，请参考官方链接

2017-01-31 15:57:00

如果你不介意分两次操作的话。

步骤:

1)使用“插入或忽略”添加新项目

2)使用“Update”更新现有项目

这两个步骤的输入都是相同的新项或可更新项的集合。适用于不需要更改的现有项目。它们将被更新，但使用相同的数据，因此净结果没有变化。

当然，更慢，等等。效率低下。是的。

易于编写sql和维护和理解它?肯定。

这是需要权衡的。适用于小的上露。对于那些不介意为代码可维护性牺牲效率的人来说非常有用。

2020-01-05 23:49:32

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

如果你这样定义你的表:

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

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

Hth……

2009-01-07 02:20:46

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

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

--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
                );

2009-01-07 02:01:10

SQLite - UPSERT不插入或替换

推荐文章

最新文章

标签