database - with - update or insert if not exists sqlite




SQLite UPSERT/UPDATE O INSERT (6)

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.

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    |

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

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

Ti farò uno migliore che non richiede la bruta-forza 'ignorare' che funzionerebbe solo se ci fosse una 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 (i.e. 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.


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




upsert