Inleiding tot de Delete-opdracht

De DELETE-opdracht wordt gebruikt om rijen uit een SQL Server-gegevenstabel te verwijderen. In dit artikel gaan we in op het gebruik van het DELETE-commando. We bespreken enkele best practices, beperkingen en sluiten af met enkele voorbeelden.

Dit is het vierde artikel in een serie artikelen. U kunt bij het begin beginnen door Introduction to SQL Server Data Modification Statements te lezen.

Alle voorbeelden voor deze les zijn gebaseerd op Microsoft SQL Server Management Studio en de AdventureWorks2012 database. U kunt aan de slag met deze gratis tools met behulp van mijn Gids Aan de slag met SQL Server

Voor we beginnen

Hoewel dit artikel de AdventureWorks database gebruikt voor de voorbeelden, heb ik besloten om een voorbeeld tabel te maken voor gebruik binnen de database om de voorbeelden beter te illustreren. Je kunt het script dat je moet uitvoeren hier vinden.

En laten we de tabel eerst met wat gegevens vullen met het volgende INSERT statement:

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

Je kunt meer over het INSERT statement te weten komen door ons artikel Inleiding tot het INSERT Statement te lezen.

Basisstructuur van de DELETE-verklaring

De DELETE-verklaring wordt gebruikt om kolomwaarden te wijzigen.

Er zijn drie componenten voor een DELETE-verklaring:

1. De tabel waaruit u rijen wilt verwijderen.
2. De criteria die worden gebruikt om de rijen te kiezen die moeten worden verwijderd.

Het algemene formaat voor het DELECT Statement is:

DELETEFROM tableNameWHERE searchCondition…

We gaan nu een aantal voorbeeld DELETEs doen, dus als je dat nog niet hebt gedaan, voer dan het script uit om de esqlSalesPerson tabel te maken.

Eenvoudig voorbeeld – Elke rij verwijderen

Wanneer je DELETE gebruikt zonder een WHERE clause, dan verwijdert het elke rij uit de tabel. Als we elke rij uit esqlSalesPerson willen verwijderen, kunnen we de volgende opdracht uitvoeren:

DELETEFROM esqlSalesPerson

Ik zou aanraden, voor de doeleinden van ons voorbeeld, om het DELETE commando in een transactie te wikkelen, zodat je niet herhaaldelijk rijen in de voorbeeld database hoeft in te voegen. Hier zie je hetzelfde DELETE statement.

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

De eerste count statement retourneert 13 rijen; terwijl de tweede 0 retourneert. Omdat ik de transactie ROLLBACK, is de delete operatie niet permanent.

Eenvoudig voorbeeld – Een enkele rij verwijderen

Een meer realistisch use case is het verwijderen van een enkele rij. In veel toepassingen wordt dit bereikt door te filteren op een enkele rij.

Als je de primaire sleutel kent, heb je goud in handen, want de primaire sleutel is uniek en bedoeld om elke rij positief te identificeren.

Stel dat we de rij van Jillian Carson willen verwijderen. Om dat te doen, zouden we het volgende doen:

DELETEFROM esqlSalesPersonWHERE SalesPersonID = 10095

Je vraagt je misschien af hoe je de primaire sleutel zou kennen.

Je kunt je voorstellen dat als je een web-app zou hebben met een lijst van alle verkopers, dat de grid dan de volledige naam van de verkoper zou kunnen bevatten, en de omzet van het afgelopen jaar, maar verborgen in de grid zou ook hun SalesPersonID staan.

Wanneer een gebruiker een rij in het raster selecteerde en ervoor koos om de rij te verwijderen, zou de applicatie de verborgen primaire sleutel voor de rij ophalen en vervolgens het verwijdercommando naar de database sturen.

Eenvoudig voorbeeld – Meerdere rijen verwijderen

Stel dat we alleen goed presterende verkopers in de esqlSalesPerson tabel willen tonen. We willen alleen de verkopers behouden met een omzet van vorig jaar groter of gelijk aan $2.000.000.

Omdat onze tabel mensen bevat met een omzet lager dan dit bedrag, moeten we ze verwijderen door het volgende uit te voeren:

DELETEFROM esqlSalesPersonWHERE SalesLastYear > 2000000.00

U kunt het volgende commando uitvoeren om het script binnen een transactie uit te proberen:

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

Hier het resultaat van het uitvoeren van het script:

De blauwe pijl geeft aan dat er oorspronkelijk 13 records waren, en de rode de resterende waarvan de omzet groter is dan of gelijk is aan twee miljoen dollar.

U kunt ook complexere filtervoorwaarden maken. Later in het artikel zullen we laten zien hoe je een subquery kunt gebruiken. U kunt ook Booleaanse voorwaarden gebruiken in de WHERE-clausule, net zoals u zou doen met het SELECT statement.

Overwegingen bij het gebruik van het DELETE Statement

Om alle rijen in een tabel te verwijderen, gebruikt u TRUNCATE TABLE. Het is veel sneller dan DELETE omdat het wijzigingen logt. Maar er zijn belangrijke verschillen (zie DELETE is niet TRUNCATE! hieronder)

Ook moet je niet vergeten dat je de @@ROWCOUNT functie kunt gebruiken om uit te vinden hoeveel rijen er zijn verwijderd.

Foutafhandeling

Als een DELETE statement een fout geeft, worden alle rijen teruggezet naar de staat waarin ze zich bevonden voordat het statement werd uitgevoerd. Als een fout optreedt, worden geen rijen verwijderd.

Er zijn vele redenen waarom een delete-instructie kan mislukken. Enkele van de meer typische zijn:

• Orphan Rows – als een FOREIGN KEY constraint is gedefinieerd tussen twee tabellen, zoals parentTable en childTable, dan zal het DELETE van een parentTable rij een fout veroorzaken als childTable rijen gerelateerd aan de parent bestaan. De manier om dit te omzeilen is om eerst de corresponderende childTable rijen te verwijderen, dan de parentTable rij.
• Arithmetic Errors – Als een expressie evaluatie resulteert in een rekenkundige fout, zoals delen door nul, wordt de DELETE geannuleerd en geen rijen verwijderd.

Locking Gedrag

Een delete statement plaatst een exclusief (X) slot op de tabel. Dit betekent dat geen andere query de gegevens van de tabel kan wijzigen totdat de DELETE transactie is voltooid.

U kunt nog steeds gegevens lezen, maar u moet de NOLOCK hint gebruiken of het uncommitted isolatieniveau lezen.

DELETE is geen TRUNCATE!

Delete is geen TRUNCATE! Gebruik DELETE om een of meer rijen uit een tabel te verwijderen. Alleen in speciale situaties, zoals wanneer je een tabel moet terugzetten naar zijn oorspronkelijke staat, zou je TRUNCATE moeten overwegen.

Veel mensen halen DELETE en TRUNCATE door elkaar. Als u niet zeker bent, raad ik u aan mijn post te lezen over Wat is het verschil tussen Truncate en Delete in SQL Server?

Complex voorbeeld – DELETE met behulp van een SubQuery

U kunt ook meer complexe delete statement maken. Zowat elke clausule die je in een SELECT statement WHERE clausule kunt schrijven, kan in de DELETE statement’s worden geschreven, inclusief subquery’s in de WHERE clausule.

Laten we een voorbeeld doen.

Voorstel dat je alle verkopers die in de VS wonen uit de esqlSalesPerson tabel moet verwijderen. Hoewel onze tabel Stad heeft, heeft het geen land. We kunnen dit omzeilen door een subquery te gebruiken.

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

Let op, ik heb het voorbeeld in een transactie gewikkeld, zodat ik mijn testgegevens niet permanent zou wissen.

De subquery is blauw gekleurd. Merk op dat het niet verschilt van elke andere gecorreleerde subquery die je in het verleden hebt gebruikt.

De werkwijze van deze DELETE verklaring is:

1. Vind verschillende steden voor alle mensen die in de VS wonen.
2. DELETE sales people whose city is in this list.

De eerste stap resulteert uit de subquery. De tweede stap verwijdert rijen volgens de WHERE clausule.