DELETE (Transact-SQL)

Data aggiornamento: 14 aprile 2006

Elimina alcune righe da una tabella o una vista.

Convenzioni della sintassi Transact-SQL

Sintassi

[ WITH <common_table_expression> [ ,...n ] ]
DELETE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ FROM ] 
        { table_name [ WITH ( <table_hint_limited> [ ...n ] ) ]
        | view_name 
        | rowset_function_limited 
        | table_valued_function
    }
    [ <OUTPUT Clause> ]
    [ FROM <table_source> [ ,...n ] ] 
    [ WHERE { <search_condition> 
            | { [ CURRENT OF 
                   { { [ GLOBAL ] cursor_name } 
                       | cursor_variable_name 
                   } 
                ]
              }
            } 
    ] 
    [ OPTION ( <Query Hint> [ ,...n ] ) ] 
[; ]

<object> ::=
{ 
    [ server_name.database_name.schema_name. 
      | database_name. [ schema_name ] . 
      | schema_name.
    ]
        table_or_view_name 
}

Argomenti

• WITH <common_table_expression>
  Specifica il set di risultati denominato temporaneo, anche noto come espressione di tabella comune, definito nell'ambito dell'istruzione DELETE. Il set di risultati deriva da un'istruzione SELECT.

  Le espressioni di tabella comuni possono inoltre essere utilizzate con istruzioni SELECT, INSERT, UPDATE e CREATE VIEW. Per ulteriori informazioni, vedere WITH common_table_expression (Transact-SQL).

• TOP (expression) [ PERCENT ]
  Specifica il numero o la percentuale di righe casuali che verranno eliminate. expression può essere un numero o una percentuale di righe. Le righe a cui viene fatto riferimento nell'espressione TOP utilizzata con INSERT, UPDATE o DELETE non sono ordinate.

  Le parentesi che delimitano expression nell'espressione TOP sono necessarie nelle istruzioni INSERT, UPDATE e DELETE. Per ulteriori informazioni, vedere TOP (Transact-SQL).

• FROM
  Parola chiave facoltativa che è possibile specificare tra la parola chiave DELETE e l'oggetto di destinazione table_or_view_name o rowset_function_limited.

• server_name
  Nome del server (che utilizza come nome un nome di server collegato o la funzione OPENDATASOURCE) in cui è contenuta la tabella o la vista. Se viene specificato server_name, sono necessari database_name e schema_name.

• database_name
  Nome del database.

• schema_name
  Nome dello schema a cui appartiene la tabella o la vista.

• table
  Nome della tabella da cui si desidera rimuovere le righe.

  È inoltre possibile utilizzare una variabile table, nel relativo ambito, come origine della tabella in un'istruzione DELETE.

• WITH ( <table_hint_limited> [... n] )
  Specifica uno o più hint di tabella consentiti per una tabella di destinazione. La parola chiave WITH e le parentesi sono obbligatorie. Le opzioni NOLOCK e READUNCOMMITTED non sono consentite. Per ulteriori informazioni sugli hint di tabella, vedere table_hint (Transact-SQL).

• view_name
  Nome della vista da cui si desidera rimuovere le righe.

  È necessario che la vista specificata nell'argomento view_name sia aggiornabile e includa un riferimento a una sola tabella di base nella clausola FROM della vista. Per ulteriori informazioni sulle viste aggiornabili, vedere CREATE VIEW (Transact-SQL).

• rowset_function_limited
  Funzione OPENQUERY o OPENROWSET, in base alle funzionalità del provider. Per ulteriori informazioni sulle funzionalità necessarie per il provider, vedere Requisiti relativi alle istruzioni UPDATE e DELETE per i provider OLE DB.

• table_valued_function
  Può essere una funzione valutata a livello di tabella. Per ulteriori informazioni sulle funzioni valutate a livello di tabella, vedere Funzioni definite dall'utente valutate a livello di tabella.

• <OUTPUT_Clause>
  Restituisce le righe eliminate o le espressioni basate su tali righe nell'ambito di un'operazione DELETE. La clausola OUTPUT non è supportata in istruzioni DML eseguite su viste o tabelle remote. Per ulteriori informazioni, vedere Clausola OUTPUT (Transact-SQL).

• FROM <table_source>
  Specifica una clausola FROM aggiuntiva. Questa estensione Transact-SQL dell'istruzione DELETE consente di specificare dati di <table_source> e di eliminare le righe corrispondenti dalla tabella specificata nella prima clausola FROM.

  È possibile utilizzare questa estensione, specificando un join, al posto di una subquery nella clausola WHERE per identificare le righe che si desidera rimuovere.

  Per ulteriori informazioni, vedere FROM (Transact-SQL).

• WHERE
  Specifica le condizioni utilizzate per limitare il numero di righe da eliminare. Se la clausola WHERE viene omessa, l'istruzione DELETE elimina tutte le righe della tabella.

  Le operazioni di eliminazione possono essere di due diversi tipi in base al contenuto della clausola WHERE:

  • Le eliminazioni con ricerca specificano una condizione di ricerca che qualifica le righe da eliminare. Ad esempio, WHERE column_name = value.
  • Le eliminazioni posizionate utilizzano la clausola CURRENT OF per specificare un cursore. L'operazione di eliminazione viene eseguita nella posizione corrente del cursore. Questo tipo di eliminazione risulta più accurato rispetto a un'istruzione DELETE con ricerca che utilizza una clausola WHERE search_condition per qualificare le righe da eliminare. Un'istruzione DELETE con ricerca elimina più righe se la condizione di ricerca non identifica una singola riga in modo univoco.

• <search_condition>
  Specifica le condizioni di restrizione per le righe da eliminare. Non sono previsti limiti per il numero di predicati che è possibile includere in una condizione di ricerca. Per ulteriori informazioni, vedere Condizione di ricerca (Transact-SQL).

• CURRENT OF
  Specifica che l'istruzione DELETE viene eseguita nella posizione corrente del cursore specificato.

• GLOBAL
  Specifica che l'argomento cursor_name fa riferimento a un cursore globale.

• cursor_name
  Nome del cursore aperto da cui viene eseguita l'operazione di recupero. Se esistono sia un cursore globale che un cursore locale denominati cursor_name, questo argomento indica il cursore globale se è stato specificato l'argomento GLOBAL. In caso contrario, indica il cursore locale. Il cursore deve consentire operazioni di aggiornamento.

• cursor_variable_name
  Nome di una variabile di cursore. La variabile di cursore deve fare riferimento a un cursore che consente operazioni di aggiornamento.

• OPTION ( <query_hint> [ ,... n] )
  Parole chiave che indicano che vengono utilizzati hint di ottimizzazione per personalizzare la modalità di elaborazione dell'istruzione in Motore di database. Per ulteriori informazioni, vedere query_hint (Transact-SQL).

Osservazioni

È possibile utilizzare l'istruzione DELETE nel corpo di una funzione definita dall'utente se l'oggetto che si desidera modificare è una variabile table.

L'istruzione DELETE può avere esito negativo se viola un trigger o tenta di rimuovere una riga a cui fanno riferimento i dati di un'altra tabella contenente un vincolo FOREIGN KEY. Se l'istruzione DELETE tenta di rimuovere più righe e l'eliminazione di una qualsiasi di queste righe viola un trigger o un vincolo, l'istruzione viene annullata, viene restituito un errore e non viene rimossa alcuna riga.

Quando un'istruzione DELETE rileva un errore aritmetico, ovvero un errore di overflow, una divisione per zero o un errore di dominio, durante la valutazione di un'espressione, Motore di database gestisce l'errore come se l'opzione SET ARITHABORT fosse impostata su ON. La parte rimanente del batch viene annullata e viene visualizzato un messaggio di errore.

L'impostazione dell'opzione SET ROWCOUNT viene ignorata per le istruzioni DELETE eseguite su tabelle remote e su viste partizionate locali e remote.

Se si desidera eliminare tutte le righe di una tabella, utilizzare l'istruzione DELETE senza specificare una clausola WHERE oppure utilizzare TRUNCATE TABLE. L'esecuzione di TRUNCATE TABLE è più veloce rispetto a quella di DELETE e comporta un minor utilizzo di risorse del log delle transazioni e di sistema.

Eliminazione di righe da un heap

Quando si eliminano le righe da un heap, Motore di database può utilizzare il blocco di riga o di pagina per l'operazione. Le pagine svuotate dall'operazione di eliminazione rimangono pertanto allocate all'heap. Se le pagine vuote non vengono deallocate, non è possibile riutilizzare lo spazio associato per altri oggetti nel database.

Per eliminare le righe di un heap e deallocare le pagine, utilizzare uno dei metodi seguenti.

• Specificare l'hint TABLOCK nell'istruzione DELETE. Se si utilizza l'hint TABLOCK, l'operazione di eliminazione acquisirà un blocco condiviso sulla tabella anziché un blocco di riga o di pagina. In questo modo sarà possibile deallocare le pagine. Per ulteriori informazioni sull'hint TABLOCK, vedere table_hint (Transact-SQL).
• Utilizzare TRUNCATE TABLE se è necessario eliminare tutte le righe della tabella.
• Creare un indice cluster sull'heap prima di eliminare le righe. È possibile eliminare l'indice cluster dopo l'eliminazione delle righe. Questo metodo richiede più tempo rispetto ai precedenti e utilizza una maggiore quantità di risorse temporanee.

Per ulteriori informazioni sui blocchi, vedere Utilizzo dei blocchi in Motore di database.

Utilizzo di un trigger INSTEAD OF in operazioni DELETE

Quando viene definito un trigger INSTEAD OF per operazioni DELETE relative a una tabella o una vista, anziché l'istruzione DELETE viene eseguito il trigger. Nelle versioni precedenti di SQL Server le istruzioni DELETE e le altre istruzioni di modifica dei dati supportano solo trigger AFTER. Non è possibile specificare la clausola FROM in un'istruzione DELETE contenente un riferimento diretto o indiretto a una vista per cui è stato definito un trigger INSTEAD OF. Per ulteriori informazioni sui trigger INSTEAD OF, vedere CREATE TRIGGER (Transact-SQL).

Autorizzazioni

Sono richieste le autorizzazioni DELETE per la tabella di destinazione. Se l'istruzione contiene una clausola WHERE, sono inoltre richieste le autorizzazioni SELECT.

Le autorizzazioni DELETE vengono assegnate per impostazione predefinita ai membri del ruolo predefinito del server sysadmin e ai membri dei ruoli predefiniti del database db_owner e db_datawriter nonché al proprietario della tabella. I membri dei ruoli sysadmin, db_owner e db_securityadmin e il proprietario della tabella possono trasferire le autorizzazioni ad altri utenti.

Esempi

A. Utilizzo di DELETE senza una clausola WHERE

Nell'esempio seguente vengono eliminate tutte le righe dalla tabella SalesPersonQuotaHistory perché non viene utilizzata una clausola WHERE per limitare il numero di righe eliminate.

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

B. Utilizzo di DELETE su un set di righe

Nell'esempio seguente vengono eliminate dalla tabella ProductCostHistory tutte le righe in cui il valore della colonna StandardCost è maggiore di 1000.00.

USE AdventureWorks;
GO
DELETE FROM Production.ProductCostHistory
WHERE StandardCost > 1000.00;
GO

C. Utilizzo di DELETE sulla riga corrente di un cursore

Nell'esempio seguente viene eliminata una singola riga dalla tabella EmployeePayHistory tramite il cursore denominato complex_cursor. L'operazione di eliminazione ha effetto solo sulla riga corrente recuperata dal cursore.

USE AdventureWorks;
GO
DECLARE complex_cursor CURSOR FOR
    SELECT a.EmployeeID
    FROM HumanResources.EmployeePayHistory AS a
    WHERE RateChangeDate <> 
         (SELECT MAX(RateChangeDate)
          FROM HumanResources.EmployeePayHistory AS b
          WHERE a.EmployeeID = b.EmployeeID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
DELETE FROM HumanResources.EmployeePayHistory
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;
GO

D. Utilizzo di un'istruzione DELETE basata su una subquery e dell'estensione Transact-SQL

Nell'esempio seguente viene illustrato l'utilizzo dell'estensione Transact-SQL per eliminare alcuni record da una tabella di base basata su un join o una subquery correlata. La prima istruzione DELETE illustra l'utilizzo di una subquery compatibile con SQL-2003, mentre la seconda istruzione DELETE illustra l'estensione Transact-SQL. Entrambe le query rimuovono alcune righe dalla tabella SalesPersonQuotaHistory in base alle vendite realizzate dall'inizio dell'anno e archiviate nella tabella SalesPerson.

-- SQL-2003 Standard subquery

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
WHERE SalesPersonID IN 
    (SELECT SalesPersonID 
     FROM Sales.SalesPerson 
     WHERE SalesYTD > 2500000.00);
GO

-- Transact-SQL extension
USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
FROM Sales.SalesPersonQuotaHistory AS spqh
    INNER JOIN Sales.SalesPerson AS sp
    ON spqh.SalesPersonID = sp.SalesPersonID
WHERE sp.SalesYTD > 2500000.00;
GO

E. Utilizzo di DELETE con la clausola TOP

Nell'esempio seguente viene eliminato dalla tabella ProductInventory il 2.5% delle righe, pari a 27 righe.

USE AdventureWorks;
GO
DELETE TOP (2.5) PERCENT 
FROM Production.ProductInventory;
GO

F. Utilizzo di DELETE con la clausola OUTPUT

Nell'esempio seguente vengono eliminate tutte le righe nella tabella Sales.ShoppingCartItem. La clausola OUTPUT DELETED.* INTO @MyTableVar specifica che i risultati dell'istruzione DELETE, ovvero tutte le colonne delle righe eliminate, devono essere restituiti alla variabile table@MyTableVar. Questa clausola è seguita da due istruzioni SELECT che restituiscono i valori in @MyTableVar e i risultati dell'operazione di eliminazione nella tabella ShoppingCartItem.

USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.* ;

--Verify all rows in the table have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem;
GO

Di seguito sono riportati i set di risultati delle istruzioni SELECT:

ShoppingCartItemID  ShoppingCartID  Quantity  ProductID 
------------------  --------------  --------  ---------
2                   14951           3         862
5                   20621           7         874
4                   20621           4         881

(3 row(s) affected)

Rows in Table 
------------- 
0

(1 row(s) affected)

G. Utilizzo di OUTPUT con from_table_name in un'istruzione DELETE

Nell'esempio seguente vengono eliminate alcune righe nella tabella ProductProductPhoto in base ai criteri di ricerca definiti nella clausola FROM dell'istruzione DELETE. La clausola OUTPUT restituisce le colonne della tabella che si desidera eliminare, DELETED.ProductID, DELETED.ProductPhotoID e alcune colonne della tabella Product. Queste informazioni vengono utilizzate nella clausola FROM per specificare le righe da eliminare.

USE AdventureWorks
GO
DECLARE @MyTableVar table (
    ProductID int NOT NULL, 
    ProductName nvarchar(50)NOT NULL,
    ProductModelID int NOT NULL, 
    PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
       p.Name,
       p.ProductModelID,
       DELETED.ProductPhotoID
    INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p 
    ON ph.ProductID = p.ProductID 
    WHERE p.ProductModelID BETWEEN 120 and 130;

--Display the results of the table variable.
SELECT ProductID, ProductName, ProductModelID, PhotoID 
FROM @MyTableVar
ORDER BY ProductModelID;
GO

Vedere anche

Riferimento

CREATE TRIGGER (Transact-SQL)
INSERT (Transact-SQL)
SELECT (Transact-SQL)
TRUNCATE TABLE (Transact-SQL)
UPDATE (Transact-SQL)
WITH common_table_expression (Transact-SQL)

Altre risorse

Eliminazione di dati in una tabella

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche