sqlite update or insert




SQLite-UPSERT*不是* INSERT或REPLACE (10)

http://en.wikipedia.org/wiki/Upsert

在SQL Server上插入更新存储过程

在SQLite中有没有想到的一些聪明的方法?

基本上,如果记录存在,我想更新四列中的三列。如果它不存在,我想用第四列的默认值(NUL)插入记录。

该ID是一个主键,因此只有一个记录要输入到UPSERT中。

(我试图避免SELECT的开销,以确定是否需要更新或INSERT显然)

建议?

我无法确认SQLite站点上的TABLE CREATE语法。 我还没有建立一个演示来测试它,但它似乎不被支持..

如果是这样,我有三列,所以它实际上看起来像:

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    Blob1 BLOB ON CONFLICT REPLACE, 
    Blob2 BLOB ON CONFLICT REPLACE, 
    Blob3 BLOB 
);

但前两个blob不会导致冲突,只有ID会因此我的asusme Blob1和Blob2不会被替换(根据需要)

当绑定数据是一个完整的事务时,在SQLite中进行更新,这意味着每个要更新的发送行都需要:Prepare / Bind / Step / Finalize语句,与允许使用重置功能的INSERT不同

声明对象的生命是这样的:

  1. 使用sqlite3_prepare_v2()创建对象
  2. 使用sqlite3_bind_接口将值绑定到主机参数。
  3. 通过调用sqlite3_step()来运行SQL
  4. 使用sqlite3_reset()重置语句,然后返回到步骤2并重复。
  5. 使用sqlite3_finalize()销毁语句对象。

更新我猜测与INSERT相比较慢,但它与使用主键的SELECT相比如何?

也许我应该使用select读取第4列(Blob3),然后使用REPLACE编写一个新记录,将原始第4列与前3列的新数据混合?


INSERT或REPLACE 等于“UPSERT”。

假设我的表Employee具有字段id,名称和角色:

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

Boom,你已经失去了第一号员工的名字.SQLite已经用默认值取代了它。

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


可能对SQLITE无效(至少在android和WebSQL中不起作用)

我知道我迟到了,但......

UPDATE employee SET role = 'code_monkey', name='fred' WHERE id = 1;
INSERT INTO employee(id, role, name) values (1, 'code monkey, 'fred') WHERE changes() = 0;

所以它会尝试更新,如果记录存在,则更改()== 1,以便插入操作不被执行。

或者:

另一种完全不同的方式是:在我的应用程序中,我将内存rowID设置为long.MaxValue,当我在内存中创建行时。 (MaxValue永远不会被用作一个ID,你不会活得够长....然后,如果rowID不是那个值,那么它必须已经在数据库中,所以如果它是MaxValue,那么需要一个UPDATE,那么它需要一个插入。这只有在您可以跟踪应用中的rowID时才有用。


刚刚读过这篇文章,并且对这个“UPSERT”不太容易,我进一步调查了......

您实际上可以在SQLITE中直接轻松地完成此操作。

而不是使用: INSERT INTO

使用: INSERT OR REPLACE INTO

这正是你想要它做的!


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

  1. 开始交易
  2. 做更新
  3. 检查行数
  4. 如果它是0,则执行插入操作
  5. 承诺

如果你通常在做插入,我会的

  1. 开始交易
  2. 尝试插入
  3. 检查主键违规错误
  4. 如果我们得到一个错误做更新
  5. 承诺

这样你避免了选择,你在Sqlite上的事务性声音。


如果有人想阅读我在科尔多瓦的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);
    });
});
}

然后以编程方式构建交易。

“值”是您之前应该构建的数组,它代表要插入或更新到表中的行。

“remoteid”是我用作参考的id,因为我正在与远程服务器同步。

有关SQLite Cordova插件的使用,请参阅官方link


我想这可能是你要找的东西: ON CONFLICT条款

如果你像这样定义你的表格:

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

现在,如果你使用已经存在的ID进行INSERT,SQLite会自动执行UPDATE而不是INSERT。

心连心...


我知道的最好方法是进行更新,然后插入。 “选择的开销”是必要的,但它不是一个可怕的负担,因为你正在搜索主键,这很快。

你应该可以用你的表格和字段名称来修改下面的语句来做你想做的事情。

--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选项,只更新少量列或插入不满足的行。

在这种情况下,想象应该更新标题和内容,在存在时保留其他旧值并在未找到名称时插入提供的值:

注意INSERT被认为是自动增量时, id被强制为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


这里的解决方案实际上是UPSERT(UPDATE或INSERT)而不是INSERT OR REPLACE(在许多情况下工作方式不同)。

它是这样工作的:
1.尝试更新是否存在具有相同ID的记录。
2.如果更新没有更改任何行( NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0) ),则插入记录。

因此,现有记录已更新或将执行插入。

重要的细节是使用changes()SQL函数检查update语句是否碰到任何现有记录,如果没有碰到任何记录,只执行insert语句。

有一点需要注意的是,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;

通过扩展share您可以从一个虚拟的“单身”表(一行自己创建的表格)中进行选择。 这避免了一些重复。

我还将示例保存在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";




upsert