DELETE (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 ]
    [ FROMtable_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 le jeu 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. Pour plus d'informations, consultez WITH common_table_expression (Transact-SQL).

• TOP (expression) [ PERCENT ]
  Spécifie le nombre ou le pourcentage de lignes aléatoires qui seront supprimées. expression peut être un nombre de lignes ou un pourcentage du nombre de lignes. Les lignes référencées dans l'expression TOP utilisée avec DELETE ne sont pas triées dans un ordre donné.

  Les parenthèses qui entourent expression dans l'expression TOP sont obligatoires dans les instructions INSERT, UPDATE, MERGE et DELETE. Pour plus d'informations, consultez TOP (Transact-SQL).

• FROM
  Mot clé facultatif qui peut être inséré entre le mot clé DELETE et l'objet cible.

• 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 lié sur lequel se trouve la table ou la vue. server_name peut être spécifié en tant que nom de serveur lié ou à l'aide de la fonction OPENDATASOURCE.

  Lorsque server_name est spécifié en tant que serveur lié, database_name et schema_name sont obligatoires. Lorsque server_name est spécifié avec OPENDATASOURCE, database_name et schema_name peuvent ne pas s'appliquer à toutes les sources de données ; par ailleurs, ils dépendent des fonctionnalités du fournisseur OLE DB qui accède à l'objet distant. Pour plus d'informations, consultez Requêtes distribuées.

• database_name
  Nom de la base de données.

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

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

  La vue référencée par table_or_view_name doit pouvoir être mise à jour et doit référencer exactement une table de base dans la clause FROM de la vue. Pour plus d'informations sur les vues qu'il est possible de mettre à jour, consultez CREATE VIEW (Transact-SQL).

• rowset_function_limited
  Fonctions OPENQUERY ou OPENROWSET, en fonction des possibilités du fournisseur. Pour plus d'informations sur les possibilités nécessaires au fournisseur, consultez Conditions associées aux instructions UPDATE et DELETE pour les fournisseurs OLE DB.

• 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).

• @table\_variable
  Spécifie une variable table.

• <OUTPUT_Clause>
  Retourne des lignes supprimées ou des expressions basées sur ces lignes au cours de l'opération de suppression. La clause OUTPUT n'est pas prise en charge dans les instructions DML qui ciblent des vues partitionnées locales, des vues partitionnées distribuées, des tables distantes ou des vues distantes. Pour plus d'informations, consultez Clause OUTPUT (Transact-SQL).

• FROM table_source
  Spécifie une clause FROM supplémentaire qui peut être utilisée pour joindre le table_or view_name cible avec <table_source> pour identifier les lignes à supprimer. Cette extension Transact-SQL à DELETE peut être utilisée à la place d'une sous-requête dans la clause WHERE.

  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
  Précise 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 de curseur. La variable de curseur doit référencer un curseur qui autorise les mises à jour.

• OPTION (query_hint [ ,... n] )
  Mots clés indiquant que les indicateurs d'optimiseur sont 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 d'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).

Prise en charge de la compatibilité

L'utilisation de SET ROWCOUNT n'affectera en rien les instructions DELETE dans la prochaine version de SQL Server. N'utilisez pas SET ROWCOUNT avec les instructions DELETE dans les nouvelles tâches de développement et modifiez les applications qui utilisent actuellement la syntaxe TOP.

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. Pour plus d'informations, consultez Utilisation de TRY...CATCH dans Transact-SQL.

L'instruction DELETE peut échouer si elle enfreint 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 Gestion des données FILESTREAM par le biais de 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

Lorsqu'une clause TOP est utilisée avec DELETE, les lignes référencées ne sont pas réorganisées dans un ordre quelconque 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 sous-sélection. Consultez la section Exemples dans la suite de cette rubrique.

La clause TOP ne peut pas être utilisée dans une instruction DELETE portant sur des vues partitionnées.

La valeur de l'option SET ROWCOUNT est ignorée pour les instructions DELETE portant sur les tables distantes et les vues partitionnées locales et distantes.

Comportement de verrouillage

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 davantage de temps que les méthodes précédentes et utilise davantage de ressources temporaires.

Pour plus d'informations sur le verrouillage, consultez Verrouillage du moteur de base de données.

Comportement de journalisation

L'instruction DELETE fait toujours l'objet d'une journalisation complète.

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 attribuées par défaut aux membres du rôle serveur fixe sysadmin, aux membres des rôles de base de données fixes db_owner et db_datawriter, ainsi qu'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.

L'exemple suivant supprime toutes les lignes de la table SalesPersonQuotaHistory parce qu'il n'y a pas de clause WHERE pour limiter le nombre de lignes supprimées.

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

Spécification des lignes à supprimer

Les exemples de cette section montrent des méthodes pour limiter le nombre de lignes supprimées.

A. Utilisation de la clause WHERE pour limiter des lignes

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

B. Utilisation de la clause TOP

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

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

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

C. Utilisation d'un curseur pour supprimer la ligne actuelle

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. Utilisation d'une sous-requête et de l'extension Transact-SQL FROM

-- 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. Utilisation d'une expression de table commune

Spécification d'objets cibles autres que les tables standard

Les exemples présentés dans cette section montrent comment supprimer des lignes en spécifiant une variable de table ou de vue.

Suppression de lignes d'une table distante

Les exemples présentés dans cette section montrent comment supprimer des lignes dans une table cible distante en utilisant un serveur lié ou une fonction d'ensemble de lignes pour référencer la table distante.

Substitution du comportement par défaut de l'optimiseur de requête à l'aide d'indicateurs

Les exemples présentés dans cette section montrent comment utiliser des indicateurs de table et des indicateurs de requête pour substituer temporairement le comportement par défaut de l'optimiseur de requête lors du traitement de l'instruction DELETE.

Attention
Étant donné que l'optimiseur de requête SQL Server sélectionne généralement le meilleur plan d'exécution pour une requête, 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é.

L'exemple suivant spécifie l'indicateur de table READPAST. Lorsque READPAST est spécifié, les verrous de niveau de ligne et de niveau de page sont ignorés, ce qui fait que le moteur de base de données ne lit pas les lignes et les pages verrouillées par d'autres transactions. Pour plus d'informations, consultez Indicateurs de table (Transact-SQL).

USE AdventureWorks;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO

Capture des résultats de l'instruction DELETE

Les exemples présentés dans cette section montrent comment utiliser la clause OUTPUT pour retourner des informations de chaque ligne affectée par une instruction DELETE, ou des expressions basées sur ces lignes. Ces résultats peuvent être retournés à l'application en cours de traitement afin d'être utilisés notamment avec des messages de confirmation, des opérations d'archivage et d'autres spécifications d'application similaires.

A. Utilisation de DELETE avec la clause OUTPUT

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

--Verify all rows in the table that match 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

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

Voir aussi

Référence

Concepts