SQLite UPSERT / UPDATE O INSERT

Devo eseguire UPSERT / INSERT o UPDATE su un database SQLite.

C’è il comando INSERT O REPLACE che in molti casi può essere utile. Ma se si desidera mantenere i propri ID con l’autoincremento sul posto a causa delle chiavi esterne, non funziona poiché elimina la riga, ne crea una nuova e di conseguenza questa nuova riga ha un nuovo ID.

Questa sarebbe la tabella:

giocatori – (chiave primaria su id, nome_utente unico)

| id | user_name | age | ------------------------------ | 1982 | johnny | 23 | | 1983 | steven | 29 | | 1984 | pepee | 40 | 

Questa è una risposta tardiva. A partire da SQLIte 3.24.0, rilasciato il 4 giugno 2018, esiste infine un supporto per la clausola UPSERT che segue la syntax di PostgreSQL.

 INSERT INTO players (user_name, age) VALUES('steven', 32) ON CONFLICT(user_name) DO UPDATE SET age=excluded.age; 

Q & A Style

Bene, dopo aver ricercato e combattuto per ore con il problema, ho scoperto che ci sono due modi per farlo, a seconda della struttura del tuo tavolo e se hai restrizioni di chiavi esterne triggerste per mantenere l’integrità. Mi piacerebbe condividere questo in un formato pulito per risparmiare un po ‘di tempo per le persone che potrebbero essere nella mia situazione.

Opzione 1: puoi permetterti di cancellare la riga

In altre parole, non hai una chiave esterna o, se li hai, il tuo motore SQLite è configurato in modo tale che non ci siano eccezioni di integrità. La strada da percorrere è INSERIRE O SOSTITUIRE . Se stai cercando di inserire / aggiornare un player il cui ID esiste già, il motore SQLite cancellerà quella riga e inserirà i dati che stai fornendo. Ora viene la domanda: cosa fare per mantenere il vecchio ID associato?

Diciamo che vogliamo UPSERT con i dati user_name = ‘steven’ e age = 32.

Guarda questo codice:

 INSERT INTO players (id, name, age) VALUES ( coalesce((select id from players where user_name='steven'), (select max(id) from drawings) + 1), 32) 

Il trucco è in coalesce. Restituisce l’ID dell’utente “steven”, se presente, e in caso contrario restituisce un nuovo ID nuovo.

Opzione 2: non puoi permetterti di cancellare la riga

Dopo aver monkeking con la soluzione precedente, mi sono reso conto che nel mio caso potrebbe finire per distruggere i dati, dal momento che questo ID funziona come una chiave esterna per l’altro tavolo. Inoltre, ho creato la tabella con la clausola ON DELETE CASCADE , il che significherebbe che eliminerebbe i dati in modo silenzioso. Pericoloso.

Quindi, ho pensato inizialmente a una clausola IF, ma SQLite ha solo CASE . E questo CASE non può essere usato (o almeno non l’ho gestito io) per eseguire una query UPDATE se EXISTS (selezionare id dai giocatori dove user_name = ‘steven’), e INSERT se non lo ha fatto. Non andare.

E poi, finalmente ho usato la forza bruta, con successo. La logica è, per ogni UPSERT che si desidera eseguire, prima eseguire un INSERT O IGNORE per assicurarsi che ci sia una riga con il nostro utente, e quindi eseguire una query UPDATE con esattamente gli stessi dati che si è tentato di inserire.

Stessi dati precedenti: user_name = ‘steven’ e age = 32.

 -- make sure it exists INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32); -- make sure it has the right data UPDATE players SET user_name='steven', age=32 WHERE user_name='steven'; 

E questo è tutto!

MODIFICARE

Come ha commentato Andy, provare a inserire prima e poi aggiornare può portare a innescare i trigger più spesso del previsto. Questo non è a mio avviso un problema di sicurezza dei dati, ma è vero che sparare eventi non necessari ha poco senso. Pertanto, una soluzione migliore sarebbe:

 -- Try to update any existing row UPDATE players SET user_name='steven', age=32 WHERE user_name='steven'; -- Make sure it exists INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32); 

Ecco un approccio che non richiede la “forza ignora” della forza bruta che funzionerebbe solo in caso di violazione della chiave. In questo modo funziona in base alle condizioni specificate nell’aggiornamento.

Prova questo…

 -- Try to update any existing row UPDATE players SET user_name='steven', age=32 WHERE user_name='steven'; -- If no update happened (ie the row didn't exist) then insert one INSERT INTO players (user_name, age) SELECT 'steven', 32 WHERE (Select Changes() = 0); 

Come funziona

La “magia” qui è che usi la clausola Where (Select Changes() = 0) per determinare se ci sono delle righe per l’inserto e dato che è basata sulla tua clausola Where , può essere per qualsiasi cosa tu definisca, non solo violazioni chiave.

Nell’esempio precedente, se non ci sono modifiche dall’aggiornamento (cioè il record non esiste) allora Changes() = 0, quindi la clausola Where nell’istruzione Insert restituisce true e una nuova riga viene inserita con i dati specificati.

Se l’ Update ha aggiornato una riga esistente, allora Changes() = 1, quindi la clausola ‘Where’ nell’Insert sarà ora falsa e quindi non verrà eseguito alcun inserimento.

Non è necessaria nessuna forza bruta.

Il problema con tutte le risposte presentate completa la mancanza di trigger (e probabilmente altri effetti collaterali) in considerazione. Soluzione come

 INSERT OR IGNORE ... UPDATE ... 

porta a entrambi i trigger eseguiti (per l’inserimento e quindi per l’aggiornamento) quando la riga non esiste.

La soluzione giusta è

 UPDATE OR IGNORE ... INSERT OR IGNORE ... 

in tal caso viene eseguita solo un’istruzione (quando la riga esiste o meno).

Per avere un puro UPSERT senza buchi (per programmatori) che non si trasmettono su chiavi univoche e altre:

 UPDATE players SET user_name="gil", age=32 WHERE user_name='george'; SELECT changes(); 

SELECT changes () restituirà il numero di aggiornamenti effettuati nell’ultima richiesta. Quindi controlla se il valore di ritorno da changes () è 0, in tal caso esegui:

 INSERT INTO players (user_name, age) VALUES ('gil', 32); 

Opzione 1: Inserisci -> Aggiorna

Se desideri evitare entrambe le changes()=0 e INSERT OR IGNORE anche se non puoi permetterti di cancellare la riga – Puoi usare questa logica;

Innanzitutto, inserire (se non esiste) e quindi aggiornare filtrando con la chiave univoca.

Esempio

 -- Table structure CREATE TABLE players ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_name VARCHAR (255) NOT NULL UNIQUE, age INTEGER NOT NULL ); -- Insert if NOT exists INSERT INTO players (user_name, age) SELECT 'johnny', 20 WHERE NOT EXISTS (SELECT 1 FROM players WHERE user_name='johnny' AND age=20); -- Update (will affect row, only if found) -- no point to update user_name to 'johnny' since it's unique, and we filter by it as well UPDATE players SET age=20 WHERE user_name='johnny'; 

Per quanto riguarda i trigger

Avviso: non l’ho provato per vedere quali trigger vengono chiamati, ma presumo quanto segue:

se la riga non esiste

  • PRIMA DI INSERIRE
  • INSERISCI usando INSTEAD OF
  • DOPO L’INSERIMENTO
  • PRIMA DI AGGIORNARE
  • AGGIORNA usando INSTEAD OF
  • DOPO L’AGGIORNAMENTO

se la riga esiste

  • PRIMA DI AGGIORNARE
  • AGGIORNA usando INSTEAD OF
  • DOPO L’AGGIORNAMENTO

Opzione 2: Inserisci o sostituisci – mantieni il tuo ID

in questo modo puoi avere un singolo comando SQL

 -- Table structure CREATE TABLE players ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_name VARCHAR (255) NOT NULL UNIQUE, age INTEGER NOT NULL ); -- Single command to insert or update INSERT OR REPLACE INTO players (id, user_name, age) VALUES ((SELECT id from players WHERE user_name='johnny' AND age=20), 'johnny', 20); 

Modifica: aggiunta opzione 2.

È anche ansible aggiungere una clausola ON CONFLICT REPLACE al vincolo univoco user_name e quindi semplicemente INSERIRE, lasciandola a SQLite per capire cosa fare in caso di conflitto. Vedi: https://sqlite.org/lang_conflict.html .

Si noti inoltre la frase riguardante i trigger di eliminazione: quando la strategia di risoluzione dei conflitti REPLACE elimina le righe per soddisfare un vincolo, elimina l’triggerszione di trigger se e solo se i trigger ricorsivi sono abilitati.

Risposta accettata non è corretta
beacause della sua 2 query
il suo complesso solo !!

questa è una semplice query 2:

 $check=query('select id from players where user_name="steven";'); if(empty($check)) { query('insert into players (user_name,age) values ("steven",32);'); } else { query('update players set age=13 where id='.$check['id'].';'); } 

  • la query è una funzione per esempio