Questo articolo sull’SQL Delete fa parte della serie SQL essential sulle istruzioni, funzioni e operazioni chiave in SQL Server.
Rimuovere una riga da una tabella si realizza attraverso un linguaggio di manipolazione dei dati (DML), utilizzando la parola chiave delete. L’operazione di cancellazione SQL è di gran lunga la più semplice di tutti i comandi DML. Nell’esecuzione del comando di cancellazione, non dobbiamo preoccuparci di ottenere alcuna forma di dati dalla tabella, e non dobbiamo preoccuparci di lavorare con i dati che riceviamo indietro dalla/e tabella/e. Diciamo semplicemente al database di cancellare uno specifico record, e lui lo fa o non lo fa. È così semplice.
-
Nota: Per saperne di più su come usare l’istruzione di cancellazione SQL, leggete l’articolo The Best Ways to Use SQL Delete Statement in a SQL Table.
Per prima cosa, rivediamo velocemente come è fatta un’istruzione di cancellazione SQL. Abbiamo bisogno di dire al database e alla tabella da dove dovrebbe cancellare i dati. È una buona idea aggiungere una clausola di condizione per impostare lo scopo della cancellazione dei dati. Altrimenti, cancellerà tutto nella tabella.
Diamo un’occhiata alla nostra tabella e alla rimozione di alcuni record.
Come cancellare le righe senza clausola where
L’esempio seguente cancella tutte le righe dalla tabella Person.Person nel database AdventureWorks2014. Non c’è nessuna restrizione applicata all’istruzione di cancellazione SQL utilizzando una clausola WHERE.
|
1
2
3
|
USE Adventureworks2014;
GO
DELETE FROM .;
|
Come cancellare le righe con la clausola where
Il seguente esempio cancella le righe dalla tabella . nel database AdventureWorks2014 in cui il valore nella colonna businessEntityID è maggiore di 30,000
|
1
2
3
4
|
USE Adventureworks2014;
GO
DELETE FROM .
WHERE businessEntityID > 30000;
|
Nota: Uno sfortunato errore che può verificarsi è quello di eseguire accidentalmente una cancellazione SQL senza clausola Where e cancellare inavvertitamente tutti i dati. Per evitare che ciò accada, si consideri l’utilizzo della funzione Execution guard di ApexSQL Complete, per avvertire di tali azioni potenzialmente dannose, prima di eseguirle. Per saperne di più: Avvisi di esecuzione
Come eliminare righe usando Top con clausola where
Il seguente esempio elimina 50 righe casuali dalla tabella Person.Person nel database AdventureWorks2014. Il valore in BusinessEntityID deve essere compreso tra 30.000 e 40,000
|
1
2
3
4
|
USE Adventureworks2014;
GO
DELETE TOP(50) FROM .
WHERE BusinessEntityID tra 30000 e 40000
|
Nota: La clausola TOP (n) quando viene utilizzata con l’istruzione SQL Delete e qualsiasi istruzione DML (cioè Select, Insert, Delete e Update), l’operazione viene eseguita su una selezione casuale di un numero di righe specificato nella clausola Top.
Come cancellare le righe duplicate
Nel mondo reale, tendiamo a raccogliere dati da diverse fonti; non è raro avere record duplicati. Un approccio al problema dei duplicati è innanzitutto quello di identificare dove si sono verificati i duplicati. Ed eseguire una query di selezione su quelle colonne.
L’esempio seguente usa l’argomento PARTITION BY per partizionare il risultato della query per tutte le colonne della tabella tb_spaceused. La funzione Row_Number () è una funzione finestra, il che significa che opera su un insieme ordinato. La clausola ORDER BY specificata nella clausola OVER ordina le righe in ogni partizione per tutte le colonne della tabella tb_spaceused.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
WITH CTE
AS (SELECT *,
ROW_NUMBER() OVER(PARTITION BY database_name,
database_size,
,
riservato,
dati,
index_size,
inutilizzato
ORDER BY database_name
) AS Row_Num
FROM tb_spaceused)
SELECT *
FROM CTE
WHERE Row_Num <> 1;
|
Sostituendo l’istruzione Select con una Delete si eliminano tutti i duplicati della tabella.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
WITH CTE
AS (SELECT *,
ROW_NUMBER() OVER(PARTITION BY database_name,
database_size,
,
riservato,
dati,
index_size,
inutilizzato
ORDER BY database_name
) AS Row_Num
FROM tb_spaceused)
–SELECT *
–FROM CTE
–WHERE Row_Num <> 1;
DELETE FROM CTE
WHERE Row_Num <> 1;
|
Come eliminare le righe utilizzando SQL sub-query
Nel seguente esempio, le righe di una tabella vengono eliminate in base ai dati di un’altra tabella. Negli esempi, le righe della tabella SalesPersonQuotaHistory sono cancellate in base alla colonna SalesYTD della tabella SalesPerson.
|
1
2
3
4
5
6
7
8
|
DELETE FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID IN
(
SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE SalesYTD > 4900000.00
);
GO
|
Come eliminare le righe utilizzando SQL Joins
In questa sezione, useremo l’istruzione SQL Delete per eliminare i dati dal database Adeventureworks2014. L’eliminazione dei dati, a prima vista, sembra banale, ma una volta che entriamo nella progettazione di un grande database le cose potrebbero non essere più le stesse e facili.
In molti casi, le tabelle sono collegate tramite una relazione di chiave primaria ed esterna. Nell’esempio seguente, possiamo vedere un uso di join per cancellare i dati da Sales.SalesPersonQuotaHistory.
|
1
2
3
4
5
|
DELETE sq
FROM Sales.SalesPersonQuotaHistory sq
INNER JOIN Sales.SalesPerson sp ON sq.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 4500000.00;
GO
|
Come cancellare le righe da una tabella remota utilizzando i server collegati e OpenQuery
L’esempio seguente utilizza l’istruzione di cancellazione SQL per cancellare le righe da una tabella remota utilizzando il server collegato chiamato, hqdbt01. Poi interroga la tabella remota usando la convenzione di denominazione dell’oggetto quattro-per cancellare le righe dalla tabella remota
|
1
2
3
|
DELETE
FROM .AdventureWorks2014..
WHERE ShiftID = 2;
|
L’esempio seguente, la tabella remota viene interrogata specificando la funzione OPENQUERY rowset insieme al comando di cancellazione.
|
1
2
3
|
DELETE OPENQUERY (hqdbt01, ‘SELECT *
FROM AdventureWorks2014.HumanResources.Department
WHERE DepartmentID = 18′);
|
Come cancellare le righe usando SSMS
Usare SQL Server Management Studio (SSMS), Graphical User Interface (GUI) per cancellare le righe comporta una ricerca manuale. In realtà, sarà molto più facile e veloce eliminare i record con una query SQL.
Andiamo avanti e localizziamo la tabella per utilizzare un’istruzione di cancellazione SQL, in questo caso, la tabella dbo.cities è selezionata. Ora, clicchiamo con il tasto destro e scegliamo Edit Top 200 rows. Questa opzione apre una finestra di query designer. Successivamente, cliccate con il tasto destro sulla finestra e selezionate Execute SQL e scrivete una nuova query che eliminerà le righe dalla tabella dbo.cities.
Nel pannello dei risultati, assicuratevi che SELECT Statement stia estraendo i record corretti prima di iniziare a cancellare le righe. Selezionate le righe e cliccate con il tasto destro e scegliete Delete per rimuovere le righe dalla tabella.
Sommario
Finora, abbiamo visto molti modi diversi di usare l’istruzione di cancellazione SQL per rimuovere i dati. Ma, c’è una lista di considerazioni da seguire mentre si usa l’istruzione di cancellazione, ed è la seguente:
- Si raccomanda sempre di iniziare con un’istruzione SELECT prima di cancellare qualcosa per essere sicuri di puntare ai record corretti. Quindi l’istruzione delete viene usata per cancellare le righe da una tabella usando la clausola where per selezionare solo le righe da cancellare
- Usate sempre un identificatore unico per localizzare le righe che dovete cancellare
- Per cancellare tutte le righe di una tabella, usate sempre TRUNCATE TABLE. TRUNCATE TABLE è più veloce di un’istruzione di cancellazione SQL e utilizza meno risorse di sistema e di transaction-log
- Per impostazione predefinita, le istruzioni DELETE inducono un blocco esclusivo (X) sulla tabella, e mantiene il blocco fino al completamento della transazione
- Un blocco esclusivo impedisce alle altre transazioni di modificare i dati; le operazioni di lettura sono consentite con l’uso del suggerimento NOLOCK o del livello di isolamento read uncommitted
- Si raccomanda di specificare il suggerimento TABLOCK nell’istruzione di cancellazione. Questo processo permette la de-allocazione delle pagine e lo spazio associato disponibile per essere riutilizzato da altri oggetti nel database
- E’ buona pratica creare un indice clustered sulla tabella heap prima di eseguire una dichiarazione di cancellazione
- Anche se molto semplice e molto potente, il risultato di una dichiarazione di cancellazione è distruttivo. Le righe cancellate non possono essere facilmente recuperate
Nota: Per recuperare le righe cancellate vedi SQL Server disaster recovery – Come recuperare velocemente i dati persi a causa di un’operazione di cancellazione involontaria
Questo è tutto per ora… Spero che ti sia piaciuto leggere questo articolo sull’istruzione di cancellazione SQL. Se hai qualche domanda, sentiti libero di commentare qui sotto.
- Autore
- Post recenti
La mia specialità sta nel progettare & implementare soluzioni di alta disponibilità e migrazioni DB multipiattaforma. Le tecnologie su cui lavoro attualmente sono SQL Server, PowerShell, Oracle e MongoDB.
Vedi tutti i post di Prashanth Jayaram
- Una rapida panoramica sul controllo dei database in SQL – 28 gennaio, 2021
- Come impostare Azure Data Sync tra i database Azure SQL e SQL Server on-premises – 20 gennaio 2021
- Come eseguire operazioni di importazione/esportazione di database Azure SQL utilizzando PowerShell – 14 gennaio 2021