Risposta

SQL offre diversi modi per aggiornare o inserire dati in una tabella:

UPDATE — modifica i record esistenti che corrispondono a una condizione.
MERGE (o UPSERT) — combina la logica di inserimento e aggiornamento in un unico comando: se la riga è trovata — aggiorna; se non lo è — aggiunge.
INSERT ... ON DUPLICATE KEY UPDATE (MySQL) o INSERT ... ON CONFLICT DO UPDATE (PostgreSQL) — inserisce una nuova riga, e in caso di conflitto sulla chiave aggiorna quella esistente.

Quando e cosa utilizzare:

Se è certo che i record non esistono ancora — utilizzare INSERT.
Per aggiornare record esistenti — UPDATE con condizioni.
Se non si sa se esiste un record, è più efficace utilizzare MERGE o UPSERT — questo semplifica e riduce il numero di query SQL.
Le operazioni UPSERT sono generalmente più veloci con un elevato numero di record e minimizzano le condizioni di competizione.

Esempio (PostgreSQL):

INSERT INTO employees(id, name, salary)
VALUES (1, 'Ivan', 100000)
ON CONFLICT (id) DO UPDATE SET salary = EXCLUDED.salary;

Impatto su prestazioni e integrità:

Moltiplici UPDATE/INSERT separati possono portare a condizioni di competizione e blocchi.
MERGE/UPSERT è più efficiente durante la migrazione di massa e supporta l'atomicità.

Domanda trabocchetto

Cosa succede se si tenta di eseguire un UPSERT su un campo unico, ma si prova a inserire e aggiornare la stessa riga nella stessa transazione?

Risposta corretta: Potrebbe verificarsi un errore di blocco/conflitto, poiché la transazione tenterà di modificare la stessa riga due volte. L'operazione non verrà completata o terminerà con un errore deadlock a seconda del DBMS.

Esempio:

BEGIN;
INSERT INTO users(id, name) VALUES (1, 'Oleg') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
UPDATE users SET name = 'Petr' WHERE id = 1;
COMMIT;

In caso di ordine errato e livello di isolamento, può verificarsi un conflitto nelle modifiche.

Storia #1

In una logica di migrazione dei dati, è stata utilizzata una combinazione di SELECT, quindi INSERT o UPDATE, che a volte portava al fatto che un'altra transazione impostava i dati prima, causando l'errore "chiave duplicata". Questo ha causato frequenti fallimenti durante l'esportazione notturna dei dati, e si è reso necessario sostituire la logica con UPSERT.

Storia #2

In un progetto su MySQL è stata applicata in modo errato l'ON DUPLICATE KEY UPDATE, il che ha ignorato in modo errato le chiavi univoche composite. Di conseguenza, parte dei dati non veniva aggiornata e apparivano duplicati. Il problema è stato identificato solo in produzione.

Storia #3

In un progetto, è stato utilizzato UPDATE invece di UPSERT per la sincronizzazione dei dati utente. Durante i guasti della connessione, si verificava la perdita di parte dei dati poiché l'aggiornamento veniva saltato per i nuovi utenti. Dopo un'analisi, sono stati completamente implementati MERGE/UPSERT.

Spiega le differenze tra i comandi UPDATE, MERGE e INSERT ... ON DUPLICATE KEY UPDATE in SQL. In quali casi è preferibile ciascun approccio e come questi strumenti influenzano le prestazioni e l'integrità dei dati?

Risposta

Domanda trabocchetto