Introduzione all’istruzione DELETE

L’istruzione DELETE è usata per rimuovere le righe da una tabella di dati SQL Server. In questo articolo esploreremo come usare l’istruzione DELETE. Discuteremo alcune best practice, le limitazioni e concluderemo con diversi esempi.

Questo è il quarto articolo di una serie di articoli. Potete iniziare dall’inizio leggendo Introduzione agli stati di modifica dei dati di SQL Server.

Tutti gli esempi di questa lezione sono basati su Microsoft SQL Server Management Studio e sul database AdventureWorks2012. Puoi iniziare ad usare questi strumenti gratuiti usando la mia guida Getting Started Using SQL Server

Prima di iniziare

Anche se questo articolo usa il database AdventureWorks per i suoi esempi, ho deciso di creare una tabella di esempio da usare all’interno del database per aiutare ad illustrare meglio gli esempi. Puoi trovare lo script che dovrai eseguire qui.

Inoltre, popoliamo inizialmente la tabella con alcuni dati usando la seguente istruzione INSERT:

WITH topSalesPerson (FullName, SalesLastYear, City, rowguid)AS (SELECT S.FirstName + ' ' + S.LastName, S.SalesLastYear, S.City ,NEWID()FROM Sales.vSalesPerson SWHERE S.SalesLastYear > 1000000)INSERT INTO esqlSalesPerson (FullName, SalesLastYear, City, rowguid) SELECT FullName, SalesLastYear, City, rowguid FROM topSalesPerson

Puoi imparare di più sull’istruzione INSERT leggendo il nostro articolo Introduzione all’istruzione INSERT.

Struttura di base dell’istruzione DELETE

L’istruzione DELETE è usata per cambiare i valori delle colonne.

Ci sono tre componenti in un’istruzione DELETE:

1. La tabella da cui vuoi rimuovere le righe.
2. I criteri usati per scegliere le righe da rimuovere.

Il formato generale per l’istruzione DELECT è:

DELETEFROM tableNameWHERE searchCondition…

Ora faremo alcuni esempi di DELETE, quindi se non lo avete ancora fatto, eseguite lo script per creare la tabella esqlSalesPerson.

Semplice esempio – cancellare ogni riga

Quando usate DELETE senza una clausola WHERE, rimuove ogni riga dalla tabella. Se vogliamo cancellare ogni riga da esqlSalesPerson potremmo eseguire:

DELETEFROM esqlSalesPerson

Vorrei raccomandare, ai fini del nostro esempio, di avvolgere il comando DELETE in una transazione in modo da non dover inserire ripetutamente delle righe nel database dell’esempio. Qui vedrete la stessa istruzione DELETE.

BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonSELECT COUNT(1) FROM esqlSalesPersonROLLBACK

La prima istruzione di conteggio restituisce 13 righe; mentre la seconda restituisce 0. Poiché io ROLLBACK la transazione, l’operazione di cancellazione non è permanente.

Semplice esempio – cancellare una singola riga

Un caso d’uso più realistico è cancellare una singola riga. In molte applicazioni, questo si ottiene filtrando una singola riga.

Se conoscete la chiave primaria, siete a posto, poiché la chiave primaria è unica e ha lo scopo di identificare positivamente ogni riga.

Supponiamo di voler cancellare la riga di Jillian Carson. Per fare ciò dovremmo emettere quanto segue:

DELETEFROM esqlSalesPersonWHERE SalesPersonID = 10095

Potreste chiedervi come potreste conoscere la chiave primaria.

Potreste immaginare se aveste una web app che elenca ogni persona di vendita, che la griglia potrebbe contenere il nome completo della persona di vendita, e le vendite dell’ultimo anno, ma nascosto nella griglia sarebbe anche il loro SalesPersonID.

Quando un utente seleziona una riga sulla griglia, e sceglie di rimuoverla, l’applicazione recupera la chiave primaria nascosta per la riga, e poi invia il comando di cancellazione al database.

Semplice esempio – Cancellare più righe

Supponiamo di voler mostrare solo i venditori più performanti nella tabella esqlSalesPerson. Vogliamo mantenere solo i venditori con vendite dell’ultimo anno maggiori o uguali a $2.000.000.

Siccome la nostra tabella contiene quelli con meno di questo importo, abbiamo bisogno di rimuoverli eseguendo il seguente:

DELETEFROM esqlSalesPersonWHERE SalesLastYear > 2000000.00

Puoi eseguire il seguente comando per provare lo script all’interno di una transazione:

BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonWHERE SalesLastYear < 2000000.00SELECT FullName, SalesLastYearFROM esqlSalesPersonROLLBACK

Ecco il risultato dell’esecuzione dello script:

La freccia blu mostra che originariamente c’erano 13 record, e la rossa i rimanenti le cui vendite sono maggiori o uguali a due milioni di dollari.

Puoi anche creare condizioni di filtraggio più complesse. Più avanti nell’articolo mostreremo come usare una subquery. Puoi anche usare condizioni booleane nella clausola WHERE, proprio come faresti con l’istruzione SELECT.

Considerazioni sull’uso dell’istruzione DELETE

Per cancellare tutte le righe di una tabella, usa TRUNCATE TABLE. È molto più veloce di DELETE perché registra le modifiche. Ma ci sono differenze chiave (vedi DELETE non è TRUNCATE! sotto)

Inoltre, non dimenticare che puoi usare la funzione @@ROWCOUNT per scoprire quante righe sono state cancellate.

Gestione degli errori

Se un’istruzione DELETE genera un errore tutte le righe vengono riportate al loro stato precedente all’esecuzione dell’istruzione. Se viene generato un errore nessuna riga viene rimossa.

Ci sono molte ragioni per cui un’istruzione di cancellazione può fallire. Alcune delle più tipiche includono:

• Righe orfane – se un vincolo FOREIGN KEY è definito tra due tabelle, come parentTable e childTable, allora DELETE una riga parentTable causerà un errore se esistono righe childTable relative al genitore. Il modo per aggirare questo problema è quello di rimuovere prima le righe childTable corrispondenti, poi la riga parentTable.
• Errori aritmetici – Se la valutazione di un’espressione risulta in un errore aritmetico, come dividere per zero, il DELETE viene annullato e nessuna riga rimossa.

Comportamento di blocco

Una dichiarazione di cancellazione pone un blocco esclusivo (X) sulla tabella. Questo significa che nessun’altra query può modificare i dati della tabella fino al completamento della transazione DELETE.

È ancora possibile leggere i dati, ma è necessario utilizzare il suggerimento NOLOCK o il livello di isolamento read uncommitted.

DELETE non è TRUNCATE!

Delete non è TRUNCATE! Usate DELETE per rimuovere una o più righe da una tabella. Solo in situazioni speciali, come quando hai bisogno di riportare una tabella al suo stato iniziale, dovresti considerare TRUNCATE.

Molte persone confondono DELETE e TRUNCATE. Se non siete sicuri, vi consiglio di leggere il mio post su Qual è la differenza tra troncare e cancellare in SQL Server?

Esempio complesso – CANCELLARE usando una subquery

Potete anche creare dichiarazioni di cancellazione più complesse. Quasi tutte le clausole che potete scrivere in una clausola WHERE dell’istruzione SELECT, possono essere scritte nell’istruzione DELETE, incluse le subquery nella clausola WHERE.

Facciamo un esempio.

Supponiamo di dover rimuovere tutte le persone che vivono negli Stati Uniti dalla tabella esqlSalesPerson. Anche se la nostra tabella ha City, non ha country. Possiamo aggirare questo problema usando una subquery.

BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonWHERE esqlSalesPerson.City IN (SELECT DISTINCT City FROM Person.Address A INNER JOIN Person.StateProvince S ON A.StateProvinceID = S.StateProvinceID AND S.CountryRegionCode = 'US' )SELECT FullName, SalesLastYearFROM esqlSalesPersonROLLBACK

Nota bene, ho avvolto l’esempio in una transazione per non cancellare definitivamente i miei dati di prova.

La subquery è colorata di blu. Notate che non è diversa da qualsiasi altra subquery correlata che potreste aver usato in passato.

La meccanica di questa dichiarazione DELETE è:

1. Trova città distinte per tutte le persone che vivono negli Stati Uniti.
2. Cancella le persone delle vendite la cui città è in questa lista.

Il primo passo risulta dalla subquery. Il secondo passo elimina le righe secondo la clausola WHERE.