sql - update - upsert中文




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


我意識到這是一個古老的線程,但最近我一直在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子句,但似乎有伎倆。 我的頭腦裡也有這樣的想法,即如果對變化()的調用大於零,sqlite可以完全優化更新語句。 不管它是否真的這樣做超出了我的知識範圍,但一個人可以夢想不到他? ;)

對於獎勵積分,您可以附加這一行,返回行的id,無論是新插入的行還是現有的行。

select case changes() WHEN 0 THEN last_insert_rowid() else <primaryKeyValue> end;

我認為這可能是你正在尋找的東西: ON CONFLICT條款

如果你像這樣定義你的表格:

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

現在,如果你使用已經存在的ID進行INSERT,SQLite會自動執行UPDATE而不是INSERT。

心連心...


繼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


這種方法混合了一些其他方法從回答這個問題,並結合使用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函數中替換空值來確定要使用的默認值。


通過擴展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