update - sqlite replace row




SQLite-UPSERT*not*INSERT o REPLACE (12)

A partire dalla versione 3.24.0 UPSERT è supportato da SQLite.

Dalla documentation :

UPSERT è una speciale aggiunta alla sintassi di INSERT che fa sì che INSERT si comporti come un UPDATE o un no-op se INSERT violerebbe un vincolo di unicità. UPSERT non è SQL standard. UPSERT in SQLite segue la sintassi stabilita da PostgreSQL. La sintassi di UPSERT è stata aggiunta a SQLite con la versione 3.24.0 (in attesa).

Un UPSERT è una normale istruzione INSERT seguita dalla speciale clausola ON CONFLICT

Fonte immagine:

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

Inserisci Aggiorna stored proc su SQL Server

C'è un modo intelligente per farlo in SQLite a cui non ho pensato?

Fondamentalmente voglio aggiornare tre delle quattro colonne se il record esiste, se non esiste voglio INSERIRE il record con il valore predefinito (NUL) per la quarta colonna.

L'ID è una chiave primaria, quindi ci sarà sempre un solo record per UPSERT.

(Sto cercando di evitare il sovraccarico di SELECT per determinare se ho bisogno di UPDATE o INSERT ovviamente)

Suggerimenti?

Non posso confermare la sintassi sul sito SQLite per TABLE CREATE. Non ho creato una demo per testarlo, ma non sembra supportato ..

Se lo fosse, ho tre colonne quindi sarebbe in realtà:

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

ma i primi due blob non causeranno un conflitto, solo l'ID verrebbe quindi dimenticato Blob1 e Blob2 non saranno sostituiti (come desiderato)

AGGIORNAMENTI in SQLite quando i dati di binding sono una transazione completa, ovvero ogni riga inviata da aggiornare richiede: Prepara / Bind / Step / Finalizza le istruzioni a differenza di INSERT che consente l'uso della funzione di reset

La vita di un oggetto statement è qualcosa del genere:

  1. Crea l'oggetto usando sqlite3_prepare_v2 ()
  2. Associare valori ai parametri host utilizzando le interfacce sqlite3_bind_.
  3. Esegui SQL chiamando sqlite3_step ()
  4. Resettare l'istruzione usando sqlite3_reset (), quindi tornare al punto 2 e ripetere.
  5. Distruggi l'oggetto statement usando sqlite3_finalize ().

UPDATE Sto indovinando è lento rispetto a INSERT, ma come si confronta a SELECT utilizzando la chiave primaria?

Forse dovrei usare la selezione per leggere la quarta colonna (Blob3) e quindi usare REPLACE per scrivere un nuovo record che unisce la quarta colonna originale con i nuovi dati per le prime 3 colonne?


Avendo appena letto questo thread e sono rimasto deluso dal fatto che non fosse facile solo a questo "UPSERT", ho investigato ulteriormente ...

Puoi farlo direttamente e facilmente in SQLITE.

Invece di usare: INSERT INTO

Usa: INSERT OR REPLACE INTO

Questo fa esattamente quello che vuoi che faccia!


Espandendo share puoi selezionare da una tabella fittizia "singleton" (una tabella di tua creazione con una singola riga). Questo evita alcune duplicazioni.

Ho anche mantenuto l'esempio portatile su MySQL e SQLite e ho usato una colonna 'date_added' come esempio di come impostare una colonna solo la prima volta.

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

INSERIMENTO O SOSTITUZIONE NON è equivalente a "UPSERT".

Supponiamo che io abbia la tabella Employee con i campi id, name e role:

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, hai perso il nome del numero di impiegato 1. SQLite lo ha sostituito con un valore predefinito.

L'output atteso di un UPSERT sarebbe quello di cambiare il ruolo e mantenere il nome.


Mi rendo conto che questo è un thread vecchio, ma ho lavorato in sqlite3 ultimamente e ho trovato questo metodo più adatto alle mie esigenze di generazione dinamica di query con parametri:

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

Sono ancora 2 le query con una clausola where sull'aggiornamento, ma sembra fare il trucco. Ho anche questa idea nella mia testa che sqlite può ottimizzare completamente la dichiarazione di aggiornamento se la chiamata a changes () è maggiore di zero. Che lo faccia o no in realtà è al di là delle mie conoscenze, ma un uomo può sognare non può? ;)

Per i punti bonus è possibile aggiungere questa riga che restituisce l'id della riga sia che si tratti di una riga appena inserita o di una riga esistente.

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

Penso che questo potrebbe essere quello che stai cercando: ON CONFLICT clause .

Se definisci la tua tabella in questo modo:

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

Ora, se si esegue un INSERT con un ID già esistente, SQLite esegue automaticamente UPDATE anziché INSERT.

Hth ...


Questo metodo rimescola alcuni degli altri metodi di risposta per questa domanda e incorpora l'uso di CTE (Common Table Expressions). Introdurrò la domanda quindi spiegherò perché ho fatto quello che ho fatto.

Vorrei cambiare il cognome del dipendente 300 in DAVIS se c'è un dipendente 300. Altrimenti, aggiungerò un nuovo dipendente.

Nome tabella: colonne dipendenti: id, first_name, last_name

La query è:

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
;

Fondamentalmente, ho usato il CTE per ridurre il numero di volte che l'istruzione select deve essere utilizzata per determinare i valori predefiniti. Poiché si tratta di un CTE, selezioniamo semplicemente le colonne che vogliamo dalla tabella e l'istruzione INSERT usa questo.

Ora puoi decidere quali valori predefiniti vuoi utilizzare sostituendo i valori null, nella funzione COALESCE con quali dovrebbero essere i valori.


Se qualcuno vuole leggere la mia soluzione per SQLite in Cordova, ho ottenuto questo metodo js generico grazie alla risposta di @david sopra.

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

Quindi, per prima cosa prendi i nomi delle colonne con questa funzione:

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

Quindi creare le transazioni al livello di programmazione.

"Valori" è una matrice che dovresti costruire prima e rappresenta le righe che vuoi inserire o aggiornare nella tabella.

"remoteid" è l'ID che ho usato come riferimento, dal momento che sto eseguendo la sincronizzazione con il mio server remoto.

Per l'uso del plugin Cordova di SQLite, fare riferimento al link ufficiale


Seguendo Aristotele Pagaltzis e l'idea di COALESCE dalla COALESCE di Eric B , qui è un'opzione di aggiornamento per aggiornare solo poche colonne o inserire la riga completa se non esiste.

In questo caso, immagina che titolo e contenuto debbano essere aggiornati, mantenendo gli altri vecchi valori quando esistenti e inserendo quelli forniti quando il nome non è stato trovato:

NOTA id è forzato ad essere NULL quando INSERT come dovrebbe essere autoincrement. Se è solo una chiave primaria generata, è possibile utilizzare anche COALESCE (vedere commento di Aristotele 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;

Quindi la regola generale sarebbe, se si desidera mantenere i valori precedenti, utilizzare COALESCE , quando si desidera aggiornare i valori, utilizzare new.fieldname


Supponendo 3 colonne nella tabella .. ID, NOME, RUOLO

BAD: questo inserirà o sostituirà tutte le colonne con nuovi valori per ID = 1:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

BAD: Questo inserirà o sostituirà 2 delle colonne ... la colonna NAME sarà impostata su NULL o il valore predefinito:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

BUONA: questo aggiornerà 2 delle colonne. Quando ID = 1 esiste, NAME non verrà modificato. Quando ID = 1 non esiste, il nome sarà predefinito (NULL).

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

Questo aggiornerà 2 delle colonne. Quando ID = 1 esiste, il RUOLO sarà inalterato. Quando ID = 1 non esiste, il ruolo verrà impostato su "Benchwarmer" anziché sul valore predefinito.

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

2018-05-18 STOP STAMPA.

Supporto UPSERT in SQLite! La sintassi di UPSERT è stata aggiunta a SQLite con la versione 3.24.0 (in attesa)!

UPSERT è una speciale aggiunta alla sintassi di INSERT che fa sì che INSERT si comporti come un UPDATE o un no-op se INSERT violerebbe un vincolo di unicità. UPSERT non è SQL standard. UPSERT in SQLite segue la sintassi stabilita da PostgreSQL.

So di essere in ritardo per la festa ma ....

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

Quindi tenta di aggiornare, se il record è lì allora l'inserto non è stato modificato.

in alternativa:

Un altro modo completamente diverso per farlo è: Nella mia applicazione ho impostato il mio rowID in memoria per essere long.MaxValue quando creo la riga in memoria. (MaxValue non sarà mai usato come ID che non vivrai abbastanza a lungo .... Quindi se rowID non è quel valore allora deve essere già nel database quindi ha bisogno di un UPDATE se è MaxValue allora ha bisogno di un inserto. Questo è utile solo se puoi monitorare i rowID nella tua app.


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

se COUNT(*) = 0

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

altrimenti se COUNT(*) > 0

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




upsert