Operare sui dati in MySQL: UPDATE, DELETE e TRUNCATE

In questa lezione vedremo come operare sui dati già apresenti all’interno del nostro database MySQL. Più precisamente vedremo come effettuare degli aggiornamenti (modifiche) ai dati e come effettuare cancellazioni selettive o di massa.

Aggiornare record con UPDATE

MySQL consente di effettuare operazioni di modifica sui dati mediante il comando UPDATE di SQL. Attraverso questo comando è possibile modificare il valore presente in una colonna di un record già esistente. Il comando UPDATE è utilizzato in comunione con la keyword SET in questo modo:

UPDATE amici SET cognome = 'Verdi' WHERE id = 1;

Nell’esempio abbiamo modificato il valore del record con id 1 assegnando alla colonna "cognome" il valore "Verdi".

Se non avessimo specificato il filtro mediante WHERE avremmo modificato tutti i record presenti nella tabella!

Nel nostro esempio abbiamo modificato il valore di un unica colonna ma nulla vieta di operare simultaneamente su più colonne:

UPDATE amici SET cognome = 'Verdi', nome = 'Marco' WHERE id = 1;

Per operare simultaneamente su più campi è sufficiente suddividere le coppie chiave/valore con una virgola.

E’ importante sottolineare che è possibile utilizzare UPDATE insieme alle clausole ORDER BY e LIMIT: in questo modo è possibile modificare solo i primi X record che corrispondono ad un determinato ordinamento:

UPDATE ... ORDER BY id ASC LIMIT 10;

La nostra query effettuerà un UPDATE ai primi 10 record ordinati per id ascendente.

Per quanto riguarda il valore per i campi, questo può essere specificato esplicitamente (come visto negli esempi qui sopra) oppure è possibile utilizzare delle espressioni, ad esempio:

UPDATE visite SET click = click + 1 WHERE id = 1;

Nel nostro esempio il valore del campo numerico "click" del record con id 1 di una ipotetica tabella "visite" viene settato mediante l’espressione:

click + 1

In sostanza il nuovo valore sel campo "click" sarà uguale a se stesso + 1.

E’ appena il caso di dire che abbiamo utilizzato il simbolo + ma avremmo potuto effettuare qualsiasi altro operatore matematico per operare sul valore di questo campo numerico.

Oltre alle espressioni, è anche possibile utilizzare la keyword DEFAULT in fase di assegnazione valore, ad esempio:

UPDATE utenti SET stato = DEFAULT WHERE id = 1;

Così facendo abbiamo impostato il campo "stato" di una ipotetica tabella "utenti" al valore di default che le è stato assegnato in fase di creazione della tabella.

Il comando DELETE

Il comando DELETE di SQL è utilizzato per cancellare record (righe) presenti all’interno di una tabella. Una volta eseguito il DBMS restituisce il numero di righe coinvolte nell’operazione di cancellazione. Solitamente il comando DELETE è utilizzato insieme alla clausola WHERE al fine di definire con precisione quali record cancellare. Se WHERE è omesso verranno cancellati tutti i record presenti nella tabella. Vediamo qualche esempio:

DELETE FROM amici;

Il comando qui sopra cancella tutti i record presenti nella tabella "amici". Vediamo ora, invece, come effettuare una cancellazione selettiva:

DELETE FROM amici WHERE cognome = 'Rossi';

Così facendo abbiamo eliminato tutti i contatti con quel dato cognome.

Come per UPDATE, anche con DELETE è ppssibile usare le clausole ORDER BY e LIMIT. Facciamo un esempio:

DELETE FROM amici ORDER BY cognome ASC LIMIT 5;

Con questa istruzione abbiamo cancellato i primi 5 record trovati dopo aver ordinato la tabella in base al cognome in modo ascendente (alfabetico dalla A alla Z).

E’ anche possibile specificare più di una tabella su cui effettuare simultaneamente le operazioni di cancellazione: per farlo si dovrà ricorrere alla JOIN come spiegato in questo articolo.

Svuotare una tabella: il comando TRUNCATE

Abbiamo visto che utilizzando il comando DELETE senza specificare una condizione di filtro con WHERE comporta una cancellazione di tutti i record presenti nella tabella specificata. Un simile modo di operare, tuttavia, non è considerato formalmente corretto (seppur assolutamente funzionante).

Il modo corretto per svuotare una tabella da tutti i suoi dati prevede l’utilizzo del comando TRUNCATE in questo modo:

TRUNCATE TABLE amici;

Se avete creato le vostre tabelle utilizzando InnoDB come storage engine, l’operazione TRUNCATE opererà selettivamente riga per riga (verificando la presenza di eventuali foreign keys), in caso contrario MySQL opererà una cancellazione della tabella (DROP) per poi crearne una nuova con il medesimo nome e la medesima struttura. Il valore di AUTO_INCREMENT viene riportato a 0, quindi.