Introduction à l’instruction Delete

L’instruction DELETE est utilisée pour supprimer des lignes d’une table de données SQL Server. Dans cet article, nous allons explorer comment utiliser l’instruction DELETE. Nous discutons de certaines bonnes pratiques, des limites, et nous concluons avec plusieurs exemples.

Cet article est le quatrième d’une série d’articles. Vous pouvez commencer par le début en lisant Introduction aux instructions de modification de données de SQL Server.

Tous les exemples de cette leçon sont basés sur Microsoft SQL Server Management Studio et la base de données AdventureWorks2012. Vous pouvez commencer à utiliser ces outils gratuits en utilisant mon guide Getting Started Using SQL Server

Before we Begin

Bien que cet article utilise la base de données AdventureWorks pour ses exemples, j’ai décidé de créer une table d’exemple à utiliser dans la base de données pour mieux illustrer les exemples. Vous trouverez le script que vous devrez exécuter ici.

Ainsi, alimentons initialement la table avec quelques données en utilisant l’instruction INSERT suivante :

WITH topSalesPerson (FullName, SalesLastYear, City, rowguid)AS (SELECT S.FirstName + ' ' + S.LastName, S.SalesLastYear, S.City ,NEWID()FROM Sales.vSalesPerson SWHERE S.SalesLastYear > 1000000)INSERT INTO esqlSalesPerson (FullName, SalesLastYear, City, rowguid) SELECT FullName, SalesLastYear, City, rowguid FROM topSalesPerson

Vous pouvez en savoir plus sur l’instruction INSERT en lisant notre article Introduction à l’instruction INSERT.

Structure de base de l’instruction DELETE

L’instruction DELETE est utilisée pour modifier les valeurs des colonnes.

Il y a trois composants à une instruction DELETE :

1. La table dont vous souhaitez supprimer les lignes.
2. Les critères utilisés pour choisir les lignes à supprimer.

Le format général de l’instruction DELECT est :

DELETEFROM tableNameWHERE searchCondition…

Nous allons maintenant faire quelques exemples de DELETE, donc si vous ne l’avez pas déjà fait, exécutez le script pour créer la table esqlSalesPerson.

Exemple simple – Suppression de chaque rangée

Lorsque vous utilisez DELETE sans clause WHERE, cela supprime chaque rangée de la table. Si nous voulons supprimer chaque de esqlSalesPerson, nous pourrions exécuter :

DELETEFROM esqlSalesPerson

Je recommanderais, pour les besoins de notre exemple, d’envelopper la commande DELETE dans une transaction afin de ne pas avoir à insérer de façon répétée des rangs dans la base de données d’exemple. Ici, vous verrez la même instruction DELETE.

BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonSELECT COUNT(1) FROM esqlSalesPersonROLLBACK

La première instruction de comptage renvoie 13 lignes ; alors que, la seconde renvoie 0. Comme je ROLLBACK la transaction, l’opération de suppression n’est pas permanente.

Exemple simple – Suppression d’une seule ligne

Un cas d’utilisation plus réaliste consiste à supprimer une seule ligne. Dans de nombreuses applications, ceci est réalisé en filtrant sur une seule ligne.

Si vous connaissez la clé primaire, vous êtes en or, car la clé primaire est unique et destinée à identifier positivement chaque ligne.

Supposons que nous voulons supprimer la ligne de Jillian Carson. Pour ce faire, nous émettrions ce qui suit :

DELETEFROM esqlSalesPersonWHERE SalesPersonID = 10095

Vous vous demandez peut-être comment vous pourriez connaître la clé primaire.

Vous pouvez imaginer si vous aviez une application web qui répertorie chaque vendeur, que la grille peut contenir le nom complet du vendeur, et les ventes de l’année dernière, mais caché sur la grille serait également leur SalesPersonID.

Lorsqu’un utilisateur sélectionnerait une rangée sur la grille, et choisirait de supprimer la rangée, l’application récupérerait la clé primaire cachée pour la rangée, puis émettrait la commande de suppression à la base de données.

Exemple simple – Suppression de plusieurs rangées

Supposons que nous voulons seulement montrer les vendeurs très performants dans la table esqlSalesPerson. Nous ne voulons garder que les vendeurs dont le chiffre d’affaires de l’année dernière est supérieur ou égal à 2 000 000 $.

Puisque notre table contient ceux dont le chiffre d’affaires est inférieur à ce montant, nous devons les supprimer en exécutant ce qui suit :

DELETEFROM esqlSalesPersonWHERE SalesLastYear > 2000000.00

Vous pouvez exécuter la commande suivante pour essayer le script dans une transaction :

BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonWHERE SalesLastYear < 2000000.00SELECT FullName, SalesLastYearFROM esqlSalesPersonROLLBACK

Voici le résultat de l’exécution du script :

La flèche bleue montre qu’il y avait à l’origine 13 enregistrements, et la rouge les restants dont le chiffre d’affaires est supérieur ou égal à deux millions de dollars.

Vous pouvez également créer des conditions de filtrage plus complexes. Plus loin dans l’article, nous montrerons comment utiliser une sous-requête. Vous pouvez également utiliser des conditions booléennes, dans la clause WHERE également tout comme vous le feriez avec l’instruction SELECT.

Considérations utilisant l’instruction DELETE

Pour supprimer toutes les lignes d’une table, utilisez TRUNCATE TABLE. Il est beaucoup plus rapide que DELETE car il enregistre les modifications. Mais il y a des différences clés (Voir DELETE n’est pas TRUNCATE ! ci-dessous)

Aussi, n’oubliez pas que vous pouvez utiliser la fonction @@ROWCOUNT pour savoir combien de lignes ont été supprimées.

Gestion des erreurs

Si une instruction DELETE jette une erreur, toutes les lignes sont restaurées à leur état avant l’exécution de l’instruction. Si une erreur est déclenchée, aucun rang n’est supprimé.

Il existe de nombreuses raisons pour lesquelles une instruction de suppression peut échouer. Certaines des plus typiques comprennent :

• Rangs orphelins – si une contrainte FOREIGN KEY est définie entre deux tables, telles que parentTable et childTable, alors DELETE un rang de parentTable provoquera une erreur si des rangs de childTable liés au parent existent. Ils contournent cela en supprimant d’abord les lignes childTable correspondantes, puis la ligne parentTable.
• Erreurs arithmétiques – Si une évaluation d’expression résulte en une erreur arithmétique, telle qu’une division par zéro, le DELETE est annulé et aucune ligne n’est supprimée.

Comportement de verrouillage

Une instruction de suppression place un verrou exclusif (X) sur la table. Cela signifie qu’aucune autre requête ne peut modifier les données de la table jusqu’à ce que la transaction DELETE soit terminée.

Vous pouvez toujours lire les données, mais vous devez utiliser l’indice NOLOCK ou le niveau d’isolation read uncommitted.

DELETE n’est pas TRUNCATE!

Delete n’est pas TRUNCATE ! Utilisez DELETE pour supprimer une ou plusieurs lignes d’une table. Ce n’est que dans une situation spéciale, comme lorsque vous devez réinitialiser une table à son état initial, que vous devriez envisager TRUNCATE.

Beaucoup de gens confondent DELETE et TRUNCATE. SI vous n’êtes pas sûr, je vous recommande de lire mon post sur Quelle est la différence entre tronquer et supprimer dans SQL Server ?

Exemple complexe – DELETE utilisant une sous-quête

Vous pouvez également créer une déclaration de suppression plus complexe. À peu près n’importe quelle clause que vous pouvez écrire dans une clause WHERE d’une déclaration SELECT, peut être écrite dans celle de la déclaration DELETE, y compris les sous-requêtes dans la clause WHERE.

Faisons un exemple.

Supposons que vous ayez besoin de supprimer toutes les personnes de vente qui vivent aux États-Unis de la table esqlSalesPerson. Bien que notre table ait la ville, elle n’a pas le pays. Nous pouvons contourner cela en utilisant une sous-requête.

BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonWHERE esqlSalesPerson.City IN (SELECT DISTINCT City FROM Person.Address A INNER JOIN Person.StateProvince S ON A.StateProvinceID = S.StateProvinceID AND S.CountryRegionCode = 'US' )SELECT FullName, SalesLastYearFROM esqlSalesPersonROLLBACK

Veuillez noter que j’ai enveloppé l’exemple dans une transaction afin de ne pas supprimer définitivement mes données de test.

La sous-requête est colorée en bleu. Notez qu’elle n’est pas différente de toute autre sous-requête corrélée que vous avez pu utiliser dans le passé.

La mécanique de cette instruction DELETE est :

1. Trouver des villes distinctes pour toutes les personnes qui vivent aux États-Unis.
2. DELETE les vendeurs dont la ville est dans cette liste.

La première étape résulte de la sous-requête. La deuxième étape supprime les lignes par la clause WHERE.