UPDATE SQL in SQLite: WHERE, più colonne e UPDATE FROM

UPDATE modifica le righe esistenti

INSERT aggiunge nuove righe. UPDATE modifica righe che esistono già. La forma è breve e vale la pena impararla a memoria:

UPDATE table_name
SET column = value
WHERE condition;

Un esempio funzionante:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    status TEXT DEFAULT 'active'
);

INSERT INTO users (name, email) VALUES
    ('Ada', 'ada@old.com'),
    ('Boris', 'boris@old.com'),
    ('Rosa', 'rosa@old.com');

UPDATE users
SET email = 'ada@new.com'
WHERE id = 1;

SELECT * FROM users;

SET dice cosa cambia. WHERE dice quali righe. Il resto della tabella non viene toccato.

In pratica la clausola WHERE non è facoltativa

Tecnicamente WHERE è facoltativa. In pratica, ometterla è il modo in cui chi è alle prime armi si rovina il pomeriggio:

UPDATE users SET status = 'inactive';
-- ora tutti gli utenti sono inattivi

Senza filtro, ogni riga corrisponde. SQLite obbedisce in silenzio. Scrivi sempre prima la WHERE e poi la SET: questa sola abitudine evita un sacco di incidenti.

Se non sei sicuro che la tua WHERE sia giusta, esegui prima la stessa condizione con una SELECT:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, status TEXT);
INSERT INTO users VALUES (1, 'Ada', 'active'), (2, 'Boris', 'active'), (3, 'Rosa', 'pending');

-- Anteprima delle righe che verrebbero toccate.
SELECT id, name, status FROM users WHERE status = 'pending';

-- Poi esegui l'aggiornamento con esattamente la stessa WHERE.
UPDATE users SET status = 'active' WHERE status = 'pending';

SELECT * FROM users;

Stessa condizione, due istruzioni. La SELECT è la tua prova a secco.

Aggiornare più colonne

Separa con delle virgole le assegnazioni dentro una sola SET. Una SET, tante colonne:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    stock INTEGER
);

INSERT INTO products VALUES (1, 'Widget', 9.99, 100);

UPDATE products
SET price = 12.99,
    stock = 75,
    name = 'Premium Widget'
WHERE id = 1;

SELECT * FROM products;

Un solo viaggio al database, una riga modificata, tre colonne aggiornate. Non scrivere tre istruzioni UPDATE separate quando ne basta una.

Espressioni a destra di =

Il valore dopo = non deve per forza essere un letterale. Può essere qualsiasi espressione, anche una che fa riferimento al valore attuale della colonna:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL, stock INTEGER);
INSERT INTO products VALUES
    (1, 'Widget', 10.00, 50),
    (2, 'Gadget', 20.00, 30),
    (3, 'Gizmo', 15.00, 80);

-- Aumentare i prezzi del 10%.
UPDATE products
SET price = price * 1.10;

-- Diminuire lo stock di una riga.
UPDATE products
SET stock = stock - 1
WHERE id = 2;

SELECT * FROM products;

price * 1.10 legge il prezzo esistente, lo moltiplica e riscrive il risultato. SQLite valuta il lato destro usando i valori attuali della riga prima che qualsiasi assegnazione di questa istruzione abbia effetto, quindi puoi fare riferimento a più colonne in sicurezza:

UPDATE products SET price = price * 1.10, stock = stock + price;
-- qui 'price' sul lato destro è il prezzo VECCHIO, non quello appena aggiornato.

UPDATE ... FROM: prendere valori da un'altra tabella

Da SQLite 3.33, UPDATE supporta una clausola FROM per gli aggiornamenti tra tabelle. È il modo più pulito per sincronizzare dati tra tabelle:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, total_spent REAL DEFAULT 0);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount REAL);

INSERT INTO customers (name) VALUES ('Ada'), ('Boris'), ('Rosa');
INSERT INTO orders (customer_id, amount) VALUES
    (1, 50.00), (1, 30.00),
    (2, 100.00),
    (3, 25.00), (3, 75.00);

UPDATE customers
SET total_spent = totals.sum_amount
FROM (
    SELECT customer_id, SUM(amount) AS sum_amount
    FROM orders
    GROUP BY customer_id
) AS totals
WHERE customers.id = totals.customer_id;

SELECT * FROM customers;

La subquery calcola i totali per cliente; l'UPDATE esterno unisce quei risultati a customers tramite id. Senza UPDATE ... FROM dovresti scrivere una subquery correlata per ogni colonna, molto più rumorosa.

Alcune regole da tenere a mente:

• La tabella di destinazione va dopo UPDATE, non nell'elenco del FROM.
• È la clausola WHERE a fare il join: qui non c'è la parola chiave ON.
• Se il join può trovare più di una riga nel FROM, il risultato non è definito. Assicurati che le chiavi del join producano al massimo una corrispondenza per ogni riga di destinazione.

RETURNING: vedere cosa è cambiato

SQLite (3.35+) permette a UPDATE di restituire le righe modificate nella stessa istruzione. Comodo quando la tua applicazione ha bisogno dei valori dopo l'aggiornamento senza una SELECT successiva:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products VALUES (1, 'Widget', 10.00), (2, 'Gadget', 20.00);

UPDATE products
SET price = price * 0.9
WHERE price > 15
RETURNING id, name, price;

Ricevi le righe effettivamente toccate, con i loro nuovi valori. Risparmi un viaggio al database ed elimini un'intera categoria di race condition nel codice concorrente. Più avanti in questo capitolo c'è una pagina intera su RETURNING.

UPDATE OR REPLACE: gestire i conflitti con i vincoli

Se il tuo aggiornamento violerebbe un vincolo UNIQUE, il comportamento predefinito è interrompere l'istruzione con un errore. La clausola OR ti permette di scegliere una politica diversa:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    name TEXT
);

INSERT INTO users VALUES
    (1, 'ada@x.com', 'Ada'),
    (2, 'boris@x.com', 'Boris');

-- Questo fallirebbe con una violazione del vincolo UNIQUE:
-- UPDATE users SET email = 'boris@x.com' WHERE id = 1;

-- OR REPLACE elimina la riga in conflitto, poi esegue l'aggiornamento.
UPDATE OR REPLACE users
SET email = 'boris@x.com'
WHERE id = 1;

SELECT * FROM users;

Le opzioni sono OR ABORT (predefinita), OR REPLACE, OR IGNORE, OR FAIL e OR ROLLBACK. REPLACE è quella pericolosa: elimina la riga in conflitto, e questo può propagarsi attraverso le chiavi esterne. Usala solo quando intendi davvero "se esiste già una riga con questo valore univoco, buttala via".

Per la maggior parte del lavoro in stile upsert, la sintassi dedicata INSERT ... ON CONFLICT è più chiara. C'è una pagina apposita.

Racchiudi gli aggiornamenti rischiosi in una transazione

Quando modifichi molte righe o esegui più istruzioni UPDATE che devono riuscire insieme, racchiudile in una transazione. Se qualcosa va storto, puoi fare rollback allo stato precedente:

CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT, balance REAL);
INSERT INTO accounts VALUES (1, 'Ada', 100.00), (2, 'Boris', 50.00);

BEGIN;
    UPDATE accounts SET balance = balance - 25 WHERE id = 1;
    UPDATE accounts SET balance = balance + 25 WHERE id = 2;
COMMIT;

SELECT * FROM accounts;

Se la seconda istruzione fallisce (per esempio per un vincolo), ROLLBACK annulla la prima. Senza una transazione resteresti con un trasferimento a metà: Ada con 25 in meno, Boris invariato. Le transazioni hanno un capitolo tutto loro più avanti; per ora ti basta sapere che esistono e che gli aggiornamenti massivi vanno quasi sempre dentro una transazione.

Errori comuni

Un breve elenco di cose che fanno male:

• Dimenticare WHERE: aggiorna ogni riga. Leggi la tua istruzione ad alta voce prima di eseguirla.
• Operatore sbagliato nella WHERE: WHERE status = NULL non corrisponde a niente. Usa IS NULL. Lo vediamo nella pagina sugli operatori.
• Aggiornare con una subquery che restituisce più di una riga quando te ne aspetti una. Usa LIMIT 1 o aggrega la subquery, altrimenti otterrai errori o risultati sorprendenti.
• Confondere UPDATE OR REPLACE con UPSERT. OR REPLACE elimina le righe in conflitto. INSERT ... ON CONFLICT DO UPDATE le modifica sul posto. Sono operazioni diverse.

Prossimo passo: DELETE

UPDATE modifica le righe; DELETE le rimuove. Vale la stessa disciplina con la WHERE, e la stessa abitudine di "eseguire prima una SELECT" ti salverà dagli stessi tipi di disastro. È la prossima pagina.