DELETE (Transact-SQL)

Supprime des lignes dans une table ou une vue.

Conventions de syntaxe Transact-SQL

Syntaxe

[ WITH <common_table_expression> [ ,...n ] ]
DELETE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ FROM ] 
    { <object> | rowset_function_limited 
      [ WITH ( <table_hint_limited> [ ...n ] ) ]
    }
    [ <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. L'ensemble 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_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 dans les instructions INSERT, UPDATE ou DELETE ne sont pas triées dans un ordre donné.

  Les parenthèses qui entourent expression dans TOP sont indispensables dans les instructions INSERT, UPDATE 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 la cible table_or_view_name ou rowset_function_limited.

• 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 l'argument server_name n'est pas 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 table ou la vue.

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

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

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

• <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 spécifié ; sinon, il fait référence au curseur local. Le curseur doit autoriser 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).

Notes

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

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 traitement est annulé et un message d'erreur est retourné.

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.

L'utilisation de SET ROWCOUNT n'affectera en rien les instructions DELETE, INSERT et UPDATE dans la prochaine version de SQL Server. N'utilisez pas SET ROWCOUNT avec les instructions DELETE, INSERT et UPDATE dans les nouvelles tâches de développement et pensez à modifier les applications qui l'utilisent actuellement. Il est conseillé d'utiliser la clause TOP à la place.

Si vous voulez supprimer toutes les lignes d'une table, utilisez l'instruction DELETE sans spécifier une clause WHERE ou utilisez l'instruction TRUNCATE TABLE. TRUNCATE TABLE est plus rapide et utilise moins de ressources du système et du journal des transactions que l'instruction DELETE.

Suppression de lignes d'un segment de mémoire

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.

Utilisation d'un déclencheur INSTEAD OF sur des actions DELETE

Lorsqu'un déclencheur INSTEAD OF est défini pour les actions DELETE sur une table ou une vue, le déclencheur s'exécute à la place de l'instruction DELETE. Les versions antérieures de SQL Server prennent en charge les déclencheurs AFTER seulement sur DELETE et sur les autres instructions de modification des données. 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).

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 de 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 à autres utilisateurs.

Exemples

A. Utilisation de DELETE sans clause WHERE

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 AdventureWorks2008R2;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

B. Utilisation de DELETE sur un ensemble 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 AdventureWorks2008R2;
GO
DELETE FROM Production.ProductCostHistory
WHERE StandardCost > 1000.00;
GO

C. Utilisation de DELETE sur la ligne actuelle d'un curseur

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

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

D. Utilisation de DELETE sur la base d'une sous-requête, à l'aide de l'extension Transact-SQL

L'exemple suivant utilise l'extension Transact-SQL pour supprimer des enregistrements dans une table de base qui est basée sur une jointure ou une sous-requête corrélée. La première instruction DELETE montre une sous-requête compatible ISO tandis que la seconde instruction DELETE montre l'extension Transact-SQL. Les deux requêtes suppriment des lignes de la table SalesPersonQuotaHistory en se basant sur les ventes de l'année enregistrées dans la table SalesPerson.

-- SQL-2003 Standard subquery

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

-- Transact-SQL extension
USE AdventureWorks2008R2;
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

E. Utilisation de DELETE avec la clause TOP

L'exemple suivant supprime 2.5 % des lignes (27 lignes) de la table ProductInventory.

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

F. 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 AdventureWorks2008R2;
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

G. 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 AdventureWorks2008R2;
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)

Concepts

Mise en route du stockage FILESTREAM

Autres ressources

Suppression de données dans une table