SQLite - upsert * not * INSERT or REPLACE

INSERT OR REPLACE is NOT equivalent to "UPSERT".

Powiedzmy, że mam pracownika tabeli z polami id, nazwa i rola:

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

Oczekiwanym rezultatem UPSERT będzie zmiana roli i zachowanie nazwy.

Odpowiedź Erica B jest OK, jeśli chcesz zachować tylko jedną lub może dwie kolumny z istniejącego wiersza. Jeśli chcesz zachować wiele kolumn, robi się to zbyt uciążliwe szybko.

Oto podejście, które będzie dobrze skalowane do dowolnej ilości kolumn po obu stronach. Aby to zilustrować, zakładam następujący schemat:

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

Zauważ w szczególności, że {[2] } jest naturalnym kluczem wiersza - id jest używany tylko dla kluczy obcych, więc SQLite musi wybrać ID wartość sama podczas wstawiania nowego wiersza. Ale podczas aktualizacji istniejącego wiersza na podstawie jego name, chcę, aby nadal miał starą wartość ID (oczywiście!).

Osiągam prawdziwą UPSERT z następującą konstrukcją:

 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;

Dokładna forma tego zapytania może się nieco różnić. Kluczem jest użycie INSERT SELECT z lewym zewnętrznym łączeniem, aby połączyć istniejący wiersz z nowymi wartościami.

Tutaj, jeśli wiersz wcześniej nie istniał, old.id będzie NULL i SQLite przypisze ID automatycznie, ale jeśli taki wiersz już istniał, old.id będzie miał rzeczywistą wartość i zostanie ona ponownie użyta. Tego właśnie chciałem.

Możesz również dołączyć kolumnę po obu stronach new i old, a następnie użyć np. COALESCE(new.content, old.content) w zewnętrznej SELECT, aby powiedz "Wstaw nową zawartość, jeśli była, w przeciwnym razie zachowaj starą zawartość" – np. jeśli używasz stałego zapytania i wiążesz nowe wartości z symbolami zastępczymi.

Jeśli ogólnie robisz aktualizacje, ja bym to zrobił ..

1. Rozpocznij transakcję
2. wykonaj aktualizację
3. Sprawdź liczbę wierszy
4. jeśli jest 0 zrób wstawkę
5. Commit

Jeśli ogólnie robisz wstawki to ja bym

1. Rozpocznij transakcję
2. spróbuj wstawić
3. Sprawdź błąd naruszenia klucza głównego
4. jeśli mamy błąd wykonaj aktualizację
5. Commit

W ten sposób unikasz wyboru i jesteś dźwięk transakcyjny na Sqlite.

Zdaję sobie sprawę, że to stary wątek, ale ostatnio pracowałem w sqlite3 i wymyśliłem tę metodę, która lepiej odpowiadała moim potrzebom dynamicznego generowania parametryzowanych zapytań:

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

To nadal 2 zapytania z klauzulą where w aktualizacji, ale wydaje się działać. Mam również tę wizję w głowie, że sqlite może zoptymalizować instrukcję aktualizacji całkowicie, jeśli wywołanie zmiany() jest większe niż zero. To, czy faktycznie to robi, jest poza moją wiedzą, ale człowiek może marzyć, prawda? ;)

Dla punktów bonusowych możesz dodać tę linię, która zwraca identyfikator wiersza, niezależnie od tego, czy jest to nowo wstawiony wiersz, czy istniejący wiersz.

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

2018-05-18 ZATRZYMAJ PRASĘ.

Wsparcie UPSERT w SQLite! składnia UPSERT została dodana do SQLite w wersji 3.24.0 (w oczekiwaniu) !

UPSERT jest specjalnym dodatkiem do składni INSERT, który powoduje, że INSERT zachowuje się jak UPDATE lub no-op, jeśli INSERT narusza ograniczenie unikalności. UPSERT nie jest standardowym SQL. UPSERT w SQLite jest zgodny ze składnią ustaloną przez PostgreSQL.

Wiem, że jestem spóźniony na imprezę. ale....

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

Więc próbuje zaktualizować, jeśli rekord jest tam, to insert nie jest akcją-ed.

Alternatywnie:

Inny zupełnie inny sposób to: w mojej aplikacji ustawiłem mój w pamięci rowID na długi.MaxValue podczas tworzenia wiersza w pamięci. (MaxValue nigdy nie będzie używany jako identyfikator, który nie będzie żył wystarczająco długo.... Jeśli rowID nie jest tą wartością, to musi już być w bazie danych więc wymaga aktualizacji jeśli jest MaxValue to potrzebuje insert. Jest to przydatne tylko wtedy, gdy możesz śledzić rowidy w aplikacji.

Oto rozwiązanie, które tak naprawdę jest upsert (UPDATE lub INSERT) zamiast INSERT lub REPLACE (które działa inaczej w wielu sytuacjach).

To działa tak:
1. Spróbuj zaktualizować rekord o tym samym Id.
2. Jeśli aktualizacja nie zmieniła żadnego wiersza (NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0)), to Wstaw rekord.

Więc albo istniejący rekord został zaktualizowany, albo zostanie wykonana wstawka.

Ważnym szczegółem jest użycie funkcji changes () SQL, aby sprawdzić, czy aktualizacja polecenie uderza w istniejące rekordy i wykonuje polecenie insert tylko wtedy, gdy nie trafiło w żaden rekord.

Należy wspomnieć, że funkcja changes() nie zwraca zmian wykonywanych przez wyzwalacze niższego poziomu (zobacz http://sqlite.org/lang_corefunc.html#changes ), więc pamiętaj, aby wziąć to pod uwagę.

Oto SQL...

Aktualizacja testu:

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

Insert testowy:

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

Rozszerzając na odpowiedź Arystotelesa możesz wybrać z dummy 'singleton' tabeli (tabeli własnej twórczości z jednym wierszem). Pozwala to uniknąć powielania.

Zachowałem również przenośny przykład w MySQL i SQLite i użyłem kolumny 'date_added' jako przykład, jak można ustawić kolumnę tylko za pierwszym razem.

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

Najlepsze podejście, jakie znam, to zrobić aktualizację, a następnie wstawkę. "Overhead of a select" jest konieczne, ale nie jest to straszne obciążenie, ponieważ szukasz na głównym kluczu, który jest szybki.

Powinieneś móc modyfikować poniższe instrukcje z nazwami tabel i pól, aby robić to, co chcesz.

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

Jeśli ktoś chce przeczytać moje rozwiązanie dla SQLite w Cordova, mam tę ogólną metodę js dzięki @ david odpowiedź powyżej.

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

Więc najpierw wybierz nazwy kolumn za pomocą tej funkcji:

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

Następnie buduj transakcje programowo.

"Values" jest tablicą, którą powinieneś zbudować przed i reprezentuje wiersze, które chcesz wstawić lub zaktualizować do tabeli.

"remoteid" to identyfikator, którego użyłem jako odniesienia, ponieważ Synchronizuję się z moim pilotem serwer.

Za korzystanie z SQLite Cordova plugin, proszę odnieść się do oficjalnego link

Począwszy od wersji 3.24.0 UPSERT jest obsługiwany przez SQLite.

Z dokumentacji :

UPSERT jest specjalnym dodatkiem do składni INSERT, który powoduje, że INSERT zachowuje się jak UPDATE lub no-op, jeśli INSERT narusza ograniczenie unikalności. UPSERT nie jest standardowym SQL. UPSERT w SQLite jest zgodny ze składnią ustaloną przez PostgreSQL. składnia UPSERT została dodana do SQLite w wersji 3.24.0 (oczekującej).

UPSERT to zwykły Wstaw instrukcję, po której następuje klauzula special ON CONFLICT

^{źródło obrazu: /images/content/418898/d59a3fced398fbcc6b856d6e32b9747e.gif}

Rzeczywiście można zrobić upsert w SQLite, to po prostu wygląda trochę inaczej niż jesteś przyzwyczajony. Wyglądałoby to tak:

INSERT INTO table name (column1, column2) 
VALUES ("value12", "value2") WHERE id = 123 
ON CONFLICT DO UPDATE 
SET column1 = "value1", column2 = "value2" WHERE id = 123

Myślę, że to może być to, czego szukasz: na klauzuli konfliktu .

Jeśli zdefiniujesz swoją tabelę w następujący sposób:

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

Teraz, jeśli wykonasz INSERT o id, który już istnieje, SQLite automatycznie zaktualizuje zamiast INSERT.

Hth...

Ta metoda remiksuje kilka innych metod z odpowiedzi na to pytanie i zawiera użycie CTE (Common Table Expressions). Przedstawię zapytanie, a następnie wyjaśnię, dlaczego zrobiłem to, co zrobiłem.

Nazwa tabeli: pracownicy Kolumny: id, first_name, last_name

Zapytanie to:

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
;

W zasadzie, użyłem CTE aby zmniejszyć liczbę razy, do określenia wartości domyślnych należy użyć instrukcji select. Ponieważ jest to CTE, po prostu wybieramy kolumny, które chcemy z tabeli i instrukcja INSERT używa tego.

Teraz możesz zdecydować, jakich wartości domyślnych chcesz użyć, zastępując null w funkcji COALESCE wartościami.

Podążając za Arystotelesa Pagaltzisa i ideą COALESCE z Erica B odpowiedzi , tutaj jest to opcja upsert, aby zaktualizować tylko kilka kolumn lub wstawić pełny wiersz, jeśli nie istnieje.

W tym przypadku wyobraź sobie, że tytuł i treść powinny być aktualizowane, zachowując inne stare wartości, gdy istnieją i wstawiając dostarczone, gdy nazwa nie została znaleziona:

Uwaga id jest wymuszona jako NULL, gdy INSERT, Jak ma być autoincrement. Jeśli jest to tylko generowany klucz podstawowy może być również użyty COALESCE (Zobacz komentarz Arystotelesa Pagaltzisa ).

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;

Po przeczytaniu tego wątku i rozczarowaniu, że nie było łatwo tylko do tego "UPSERT" ing, zbadałem dalej...

Możesz to zrobić bezpośrednio i łatwo w SQLITE.

Zamiast: INSERT INTO

Użycie: INSERT OR REPLACE INTO

SELECT COUNT(*) FROM table1 WHERE id = 1;

If COUNT(*) = 0

INSERT INTO table1(col1, col2, cole) VALUES(var1,var2,var3);

Else if COUNT(*) > 0

UPDATE table1 SET col1 = var4, col2 = var5, col3 = var6 WHERE id = 1;