DELETE (Transact-SQL)

Supprime une ou plusieurs lignes d'une table ou d'une vue dans SQL Server 2012.

Conventions de la syntaxe Transact-SQL

Syntaxe

[ WITH <common_table_expression> [ ,...n ] ]
DELETE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ FROM ] 
    { { table_alias
      | <object> 
      | rowset_function_limited 
      [ WITH ( table_hint_limited [ ...n ] ) ] } 
      | @table_variable
    }
    [ <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 
}

Arguments

• WITH <common_table_expression>
  Spécifie l'ensemble de résultats nommé temporaire, également appelé « expression de table commune », défini dans l'étendue de l'instruction DELETE. Le jeu de résultats est dérivé d'une instruction SELECT.

  Les expressions de table communes peuvent également s'utiliser avec les instructions SELECT, INSERT, UPDATE et CREATE VIEW. Pour plus d'informations, consultez WITH Common.

• TOP (expression) [ PERCENT ]
  Spécifie le nombre ou le pourcentage de lignes aléatoires qui seront supprimées. L'argument expression peut être un nombre ou un pourcentage de lignes. Les lignes référencées dans l'expression TOP utilisée dans les instructions INSERT, UPDATE ou DELETE ne sont pas triées dans un ordre précis. Pour plus d'informations, consultez TOP (Transact-SQL).

• FROM
  Mot clé facultatif qui peut être inséré entre le mot clé DELETE et la cible table_or_view_name ou rowset_function_limited.

• table_alias
  Alias spécifié dans la clause FROM table_source représentant la table ou la vue à partir de laquelle les lignes doivent être supprimées.

• server_name
  Nom du serveur (avec un nom de serveur lié ou la fonction OPENDATASOURCE en tant que nom de serveur) contenant la table ou la vue. Si server_name est spécifié, database_name et schema_name sont obligatoires.

• database_name
  Nom de la base de données.

• schema_name
  Nom du schéma auquel appartient la vue ou la table.

• table_or view_name
  Nom de la table ou de la vue à partir desquelles les lignes doivent être supprimées.

  Une variable de table, dans son étendue, est également utilisable comme source de la table dans une instruction DELETE.

  La vue, à laquelle fait référence table_or_view_name , doit pouvoir être mise à jour et faire référence exactement à une table de base dans la clause FROM de la définition de la vue. Pour plus d'informations sur les vues pouvant être mises à jour, consultez CREATE VIEW (Transact-SQL).

• rowset_function_limited
  Fonction OPENQUERY ou OPENROWSET, selon les possibilités du fournisseur.

• WITH ( <table_hint_limited> [... n] )
  Spécifie un ou plusieurs indicateurs de table autorisés pour une table cible. Le mot clé WITH et les parenthèses sont obligatoires. NOLOCK et READUNCOMMITTED ne sont pas autorisés. Pour plus d'informations sur les indicateurs de table, consultez Indicateurs de table (Transact-SQL).

• <OUTPUT_Clause>
  Retourne des lignes supprimées ou des expressions basées sur ces lignes au cours de l'exécution de l'opération DELETE. Aucune instruction DML ciblant des vues ou des tables distantes ne prend en charge la clause OUTPUT. Pour plus d'informations, consultez Clause OUTPUT (Transact-SQL).

• FROM table_source
  Spécifie une clause FROM supplémentaire. Cette extension Transact-SQL de l'instruction DELETE permet de spécifier des données de <table_source> et de supprimer les lignes correspondantes de la table référencée dans la première clause FROM.

  Cette extension, qui spécifie une jointure, peut être utilisée dans la clause WHERE à la place d'une sous-requête pour identifier les lignes à supprimer.

  Pour plus d'informations, consultez FROM (Transact-SQL).

• WHERE
  Spécifie les conditions limitant le nombre de lignes à supprimer. Si une clause WHERE n'est pas spécifiée, DELETE supprime toutes les lignes de la table.

  Il existe deux types d'opérations de suppression, en fonction des conditions définies dans la clause WHERE :

  • Une suppression par recherche spécifie une condition de recherche permettant de désigner les lignes à supprimer. Par exemple, WHERE column_name = value.

  • Une suppression positionnée utilise la clause CURRENT OF pour spécifier un curseur. La suppression a lieu à la position actuelle du curseur. Cette opération peut être plus précise qu'une instruction DELETE par recherche qui utilise une clause WHERE search_condition pour spécifier les lignes à supprimer. Une instruction DELETE par recherche supprime plusieurs lignes si la condition de recherche n'identifie pas de façon univoque une ligne unique.

• <search_condition>
  Spécifie les conditions de limitation applicables aux lignes à supprimer. Le nombre de prédicats inclus dans une condition de recherche est illimité. Pour plus d'informations, consultez Condition de recherche (Transact-SQL).

• CURRENT OF
  Spécifie que l'instruction DELETE s'effectue à l'emplacement actuel du curseur spécifié.

• GLOBAL
  Indique que cursor_name fait référence à un curseur global.

• cursor_name
  Nom du curseur ouvert à partir duquel a lieu l'extraction. Si un curseur global et un curseur local portent tous les deux le nom cursor_name, cet argument fait référence au curseur global si GLOBAL est précisé, et au curseur local dans tous les autres cas. Le curseur doit pouvoir gérer les mises à jour.

• cursor_variable_name
  Nom d'une variable curseur. La variable de curseur doit référencer un curseur qui autorise les mises à jour.

• OPTION ( <query_hint> [ ,... n] )
  Mots clés spécifiant les indicateurs d'optimiseur utilisés pour personnaliser le traitement de l'instruction par le moteur de base de données. Pour plus d'informations, consultez Indicateurs de requête (Transact-SQL).

Recommandations

Pour supprimer toutes les lignes dans une table, utilisez TRUNCATE TABLE. TRUNCATE TABLE est plus rapide et utilise moins de ressources du système et du journal des transactions que l'instruction DELETE.

Utilisez la fonction @@ROWCOUNT pour retourner le nombre de lignes supprimées à l'application cliente. Pour plus d'informations, consultez @@ROWCOUNT (Transact-SQL).

Gestion des erreurs

Vous pouvez implémenter la gestion des erreurs pour l'instruction DELETE en spécifiant cette dernière dans une construction TRY…CATCH.

L'instruction DELETE peut échouer si elle viole un déclencheur ou si elle essaie de supprimer une ligne référencée par des données dans une autre table avec une contrainte FOREIGN KEY. Si l'instruction DELETE supprime plusieurs lignes et qu'une de ces lignes viole un déclencheur ou une contrainte, une erreur est retournée et aucune ligne n'est supprimée.

Lorsqu'une instruction DELETE rencontre une erreur arithmétique (erreur de dépassement de capacité, de division par zéro ou de domaine) lors de l'évaluation de l'expression, le moteur de base de données gère ces erreurs comme si SET ARITHABORT avait la valeur ON. Le reste du lot est annulé et un message d'erreur est retourné.

Interopérabilité

DELETE peut s'utiliser dans le corps d'une fonction définie par l'utilisateur si l'objet modifié est une variable de table.

Lorsque vous supprimez une ligne qui contient une colonne FILESTREAM, vous supprimez également ses fichiers de système de fichiers sous-jacents. Les fichiers sous-jacents sont supprimés par le garbage collector FILESTREAM. Pour plus d'informations, consultez Accéder aux données FILESTREAM avec Transact-SQL.

Il n'est pas possible de spécifier la clause FROM dans une instruction DELETE qui référence directement ou indirectement une vue sur laquelle est défini le déclencheur INSTEAD OF. Pour plus d'informations sur les déclencheurs INSTEAD OF, consultez CREATE TRIGGER (Transact-SQL).

Limitations et restrictions

Lorsque TOP est utilisé avec DELETE, les lignes référencées ne sont pas réorganisées dans un ordre particulier et la clause ORDER BY ne peut pas être spécifiée directement dans cette instruction. Si vous devez utiliser une clause TOP pour supprimer des lignes dans un ordre chronologique significatif, vous devez associer à cette clause TOP une clause ORDER BY dans une instruction de sous-sélection. Consultez la section Exemples plus loin dans cette rubrique.

TOP ne peut pas être utilisé dans une instruction DELETE avec des vues partitionnées.

Comportement de verrouillage

Par défaut, une instruction DELETE acquiert toujours un verrou exclusif (X) sur la table qu'il modifie et maintient ce verrou jusqu'à la fin de la transaction. Un verrou exclusif (X) empêche toute autre transaction de modifier les données ; les opérations de lecture ne peuvent avoir lieu qu'avec l'indicateur NOLOCK ou le niveau d'isolation « lecture non validée ». Vous pouvez spécifier des indicateurs de table pour remplacer ce comportement par défaut pour la durée de l'instruction DELETE en spécifiant une autre méthode de verrouillage ; toutefois, nous vous recommandons de ne recourir aux indicateurs qu'en dernier ressort et seulement si vous êtes un développeur ou un administrateur de base de données expérimenté. Pour plus d'informations, consultez Indicateurs de table (Transact-SQL).

Lors de la suppression de lignes d'un segment de mémoire, le moteur de base de données peut utiliser le verrouillage de ligne ou de page. Par conséquent, les pages rendues vides par l'opération de suppression demeurent allouées au segment de mémoire. Lorsque des pages vides ne sont pas désallouées, l'espace associé ne peut pas être réutilisé par d'autres objets dans la base de données.

Pour supprimer des lignes d'un segment de mémoire et désallouer des pages, utilisez l'une des méthodes suivantes.

• Spécifiez l'indicateur TABLOCK dans l'instruction DELETE. Grâce à l'indicateur TABLOCK, l'opération de suppression utilise un verrou partagé sur la table au lieu d'un verrou de ligne ou de page. Les pages sont ainsi désallouées. Pour plus d'informations sur l'indicateur TABLOCK, consultez Indicateurs de table (Transact-SQL).

• Utilisez TRUNCATE TABLE si toutes les lignes doivent être supprimées de la table.

• Créez un index cluster sur le segment de mémoire avant de supprimer les lignes. Vous pouvez supprimer l'index cluster après avoir supprimé les lignes. Cette méthode prend plus de temps que les méthodes précédentes et utilise davantage de ressources temporaires.

Comportement de journalisation

L'instruction DELETE est toujours entièrement journalisée.

Sécurité

Autorisations

Les autorisations DELETE sont requises sur la table cible. Des autorisations SELECT sont également requises si l'instruction comporte une clause WHERE.

Les autorisations DELETE sont accordées par défaut aux membres du rôle serveur fixe sysadmin, aux rôles de base de données fixes db_owner et db_datawriter et au propriétaire de la table. Les membres des rôles sysadmin, db_owner et db_securityadmin, ainsi que le propriétaire de la table peuvent transférer des autorisations à d'autres utilisateurs.

Exemples

Syntaxe de base

Les exemples fournis dans cette section présentent les fonctionnalités de base de l'instruction DELETE en utilisant la syntaxe minimale requise.

A.Utilisation de DELETE sans clause WHERE

L'exemple suivant supprime toutes les lignes de la table SalesPersonQuotaHistorycar aucune clause WHERE ne limite le nombre de lignes supprimées.

USE AdventureWorks2012;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

Limitation des lignes supprimées

Les exemples de cette section montrent comment limiter le nombre de lignes qui seront supprimées.

A.Utilisation de la clause WHERE pour supprimer un jeu de lignes

L'exemple suivant supprime toutes les lignes de la table ProductCostHistory dans lesquelles la valeur de la colonne StandardCost est supérieure à 1000.00.

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

L'exemple suivant illustre une clause WHERE plus complexe. La clause WHERE définit deux conditions qui doivent être rencontrées pour déterminer les lignes à supprimer. La valeur dans la colonne StandardCost doit être comprise entre 12.00 et 14.00, tandis que la valeur dans la colonne SellEndDate doit être Null. L'exemple imprime également la valeur de la fonction @@ROWCOUNT afin de retourner le nombre de lignes supprimées.

USE AdventureWorks2012;
GO
DELETE Production.ProductCostHistory
WHERE StandardCost BETWEEN 12.00 AND 14.00
      AND EndDate IS NULL;
PRINT 'Number of rows deleted is ' + CAST(@@ROWCOUNT as char(3));

B.Utilisation d'un curseur pour déterminer la ligne à supprimer

L'exemple suivant supprime une ligne de la table EmployeePayHistory en utilisant un curseur nommé my_cursor. La suppression est appliquée uniquement à la ligne actuellement extraite à partir du curseur.

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

C.Utilisation de jointures et de sous-requêtes sur les données d'une table pour supprimer des lignes dans une autre table

Les exemples suivants illustrent deux façons de supprimer des lignes dans une table selon les données figurant dans une autre table. Dans les deux exemples, des lignes de la table SalesPersonQuotaHistory sont supprimées en se basant sur les ventes de l'année enregistrées dans la table SalesPerson. La première instruction DELETE montre une sous-requête compatible ISO tandis que la seconde instruction DELETE montre l'extension Transact-SQL FROM pour joindre les deux tables.

-- SQL-2003 Standard subquery

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

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

A.Utilisation de TOP pour limiter le nombre de lignes supprimées

Lorsqu'une clause TOP (n) est utilisée avec DELETE, l'opération de suppression est effectuée sur une sélection aléatoire de n lignes. L'exemple suivant supprime de manière aléatoire 20 lignes de la table PurchaseOrderDetail dont la date d'échéance est antérieure au 1er juillet 2006.

USE AdventureWorks2012;
GO
DELETE TOP (20) 
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20020701';
GO

Si vous devez utiliser une clause TOP pour supprimer des lignes dans un ordre chronologique significatif, vous devez associer à cette clause TOP une clause ORDER BY dans une instruction de sous-sélection. La requête suivante supprime les 10 lignes de la table PurchaseOrderDetail dont la date d'expiration est la plus proche. Pour garantir que seules 10 lignes sont supprimées, la colonne spécifiée dans l'instruction de sous-sélection (PurchaseOrderID) constitue la clé primaire de la table. L'utilisation d'une colonne non-clé dans l'instruction de sous-sélection peut entraîner la suppression de plus de 10 lignes si la colonne spécifiée contient des valeurs dupliquées.

USE AdventureWorks2012;
GO
DELETE FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderDetailID IN
   (SELECT TOP 10 PurchaseOrderDetailID 
    FROM Purchasing.PurchaseOrderDetail 
    ORDER BY DueDate ASC);
GO

Suppression de lignes d'une table distante

Les exemples présentés dans cette section montrent comment supprimer des lignes dans une table distante en utilisant un serveur lié ou une fonction d'ensemble de lignes pour référencer la table distante. Une table distante existe sur un serveur différent ou une instance différente de SQL Server.

A.Suppression de données dans une table distante en utilisant un serveur lié

L'exemple ci-dessous supprime des lignes dans une table distante. L'exemple commence par créer un lien vers la source de données distante en utilisant sp_addlinkedserver. Le nom du serveur lié, MyLinkServer, est ensuite spécifié dans un nom d'objet en quatre parties qui se présente sous la forme server.catalog.schema.object.

USE master;
GO
-- Create a link to the remote data source. 
-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.

EXEC sp_addlinkedserver @server = N'MyLinkServer',
    @srvproduct = N' ',
    @provider = N'SQLNCLI', 
    @datasrc = N'server_name',
    @catalog = N'AdventureWorks2012';
GO

-- Specify the remote data source using a four-part name 
-- in the form linked_server.catalog.schema.object.

DELETE MyLinkServer.AdventureWorks2012.HumanResources.Department WHERE DepartmentID > 16;
GO

B.Suppression de données dans une table distante en utilisant la fonction OPENQUERY

L'exemple suivant supprime des lignes dans une table distante en spécifiant la fonction d'ensemble de lignes OPENQUERY. Le nom de serveur lié créé dans l'exemple précédent est utilisé dans cet exemple.

DELETE OPENQUERY (MyLinkServer, 'SELECT Name, GroupName FROM AdventureWorks2012.HumanResources.Department
WHERE DepartmentID = 18');
GO

C.Suppression de données dans une table distante en utilisant la fonction OPENDATASOURCE

L'exemple suivant supprime des lignes dans une table distante en spécifiant la fonction d'ensemble de lignes OPENDATASOURCE. Spécifiez un nom de serveur valide pour la source de données en utilisant le format server_name ou server_name\instance_name.

DELETE FROM OPENDATASOURCE('SQLNCLI',
    'Data Source= <server_name>; Integrated Security=SSPI')
    .AdventureWorks2012.HumanResources.Department 
WHERE DepartmentID = 17;'

Capture des résultats de l'instruction DELETE

A.Utilisation de DELETE avec la clause OUTPUT

L'exemple suivant montre comment enregistrer les résultats d'une instruction DELETE dans une variable de table.

USE AdventureWorks2012;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* 
WHERE ShoppingCartID = 20621;

--Verify the rows in the table matching the WHERE clause have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;
GO

B.Utilisation de la clause OUTPUT avec <from_table_name> dans une instruction DELETE

L'exemple suivant supprime des lignes dans la table ProductProductPhoto en fonction de critères de recherche définis dans la clause FROM de l'instruction DELETE. La clause OUTPUT retourne les colonnes DELETED.ProductID, DELETED.ProductPhotoID de la table en cours de suppression et les colonnes de la table Product. Cette méthode s'utilise dans la clause FROM pour spécifier les lignes à supprimer.

USE AdventureWorks2012;
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

Voir aussi

Référence

CREATE TRIGGER (Transact-SQL)

INSERT (Transact-SQL)

SELECT (Transact-SQL)

TRUNCATE TABLE (Transact-SQL)

UPDATE (Transact-SQL)

WITH common_table_expression (Transact-SQL)

@@ROWCOUNT (Transact-SQL)