Introduktion till Delete-anvisningen

Delete-anvisningen används för att ta bort rader från en SQL Server-databell. I den här artikeln ska vi utforska hur man använder DELETE-anvisningen. Vi diskuterar några bästa metoder, begränsningar och avslutar med flera exempel.

Detta är den fjärde artikeln i en artikelserie. Du kan börja från början genom att läsa Introduktion till SQL Server Data Modification Statements.

Alla exempel för den här lektionen är baserade på Microsoft SQL Server Management Studio och databasen AdventureWorks2012. Du kan börja använda dessa kostnadsfria verktyg med hjälp av min guide Getting Started Using SQL Server

Before we Begin

Tyvärr används AdventureWorks-databasen i den här artikeln för exemplen, men jag har bestämt mig för att skapa en exempeltabell för användning i databasen för att bättre illustrera exemplen. Du hittar skriptet som du behöver köra här.

Det är också dags att fylla tabellen med data med hjälp av följande INSERT-anvisning:

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

Du kan lära dig mer om INSERT-anvisningen genom att läsa vår artikel Introduktion till INSERT-anvisningen.

Grundläggande struktur för DELETE-anvisningen

Den DELETE-anvisningen används för att ändra kolumnvärden.

Det finns tre komponenter i en DELETE-anvisning:

1. Tabellen som du vill ta bort rader från.
2. Kriterierna som används för att välja ut de rader som ska tas bort.

Det allmänna formatet för DELECT-anvisningen är:

DELETEFROM tableNameWHERE searchCondition…

Vi kommer nu att göra några exempel på DELETE-anvisningar, så om du inte redan har gjort det, kör skriptet för att skapa tabellen esqlSalesPerson.

Enkel exempel – tar bort varje rad

När du använder DELETE utan WHERE-klausul tas varje rad bort från tabellen. Om vi vill ta bort alla från esqlSalesPerson kan vi köra:

DELETEFROM esqlSalesPerson

Jag skulle rekommendera, för vårt exempel, att du lindar in DELETE-kommandot i en transaktion så att du inte behöver lägga in rader upprepade gånger i exempeldatabasen. Här ser du samma DELETE-kommando.

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

Det första räknekommandot returnerar 13 rader, medan det andra returnerar 0. Eftersom jag ROLLBACKar transaktionen är raderingsoperationen inte permanent.

Simpelt exempel – radering av en enskild rad

Ett mer realistiskt användningsfall är att radera en enda rad. I många tillämpningar uppnås detta genom att filtrera på en enda rad.

Om du känner till primärnyckeln är du nöjd, eftersom primärnyckeln är unik och är tänkt att identifiera varje rad på ett positivt sätt.

Antag att vi vill ta bort Jillian Carsons rad. För att göra det skulle vi utfärda följande:

DELETEFROM esqlSalesPersonWHERE SalesPersonID = 10095

Du kanske undrar hur du ska veta primärnyckeln.

Du kan föreställa dig att om du har en webbapplikation som listar alla säljare, kan rutnätet innehålla säljpersonens fullständiga namn och förra årets försäljning, men gömt i rutnätet finns också deras SalesPersonID.

När en användare väljer en rad i rutnätet och väljer att ta bort raden skulle programmet hämta den dolda primärnyckeln för raden och sedan utfärda delete-kommandot till databasen.

Enkel exempel – ta bort flera rader

Antag att vi bara vill visa högpresterande säljare i tabellen esqlSalesPerson. Vi vill bara behålla de säljare vars försäljning förra året var större än eller lika med 2 000 000 dollar.

Då vår tabell innehåller de med mindre än detta belopp måste vi ta bort dem genom att köra följande:

DELETEFROM esqlSalesPersonWHERE SalesLastYear > 2000000.00

Du kan köra följande kommando för att prova skriptet inom en transaktion:

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

Här är resultatet av att köra skriptet:

Den blå pilen visar att det ursprungligen fanns 13 poster, och den röda de återstående vars försäljning är större än eller lika med två miljoner dollar.

Du kan också skapa mer komplexa filtreringsvillkor. Senare i artikeln kommer vi att visa hur man använder en subquery. Du kan också använda booleska villkor, i WHERE-klausulen också precis som du skulle göra med SELECT-anvisningen.

Observeringar med hjälp av DELETE-anvisningen

För att radera alla rader i en tabell använder du TRUNCATE TABLE. Det är mycket snabbare än DELETE eftersom det loggar ändringar. Men det finns viktiga skillnader (se DELETE isn’t TRUNCATE! nedan)

Glöm inte heller att du kan använda funktionen @@ROWCOUNT för att ta reda på hur många rader som raderades.

Felhantering

Om ett DELETE-uttalande kastar ett fel återställs alla rader till det tillstånd som rådde innan uttalandet kördes. Om ett fel utlöses tas inga rader bort.

Det finns många anledningar till att ett delete-meddelande kan misslyckas. Några av de mer typiska är:

• Orphan Rows – om en FOREIGN KEY-begränsning är definierad mellan två tabeller, t.ex. parentTable och childTable, kommer DELETE av en rad i parentTable att orsaka ett fel om det finns rader i childTable som är relaterade till den överordnade tabellen. Det går att kringgå detta genom att först ta bort motsvarande rader i childTable och sedan raden i parentTable.
• Aritmetiska fel – Om en uttrycksutvärdering resulterar i ett aritmetiskt fel, t.ex. dividera med noll, avbryts DELETE och inga rader tas bort.

Låsningsbeteende

En delete-angivelse placerar ett exklusivt (X)-lås på tabellen. Detta innebär att ingen annan fråga kan ändra tabellens data förrän DELETE-transaktionen är klar.

Du kan fortfarande läsa data, men du måste använda NOLOCK-hänvisningen eller läsa ocommitted-isoleringsnivån.

DELETE är inte TRUNCATE!

Delete är inte TRUNCATE! Använd DELETE för att ta bort en eller flera rader från en tabell. Endast i speciella situationer, t.ex. när du behöver återställa en tabell till sitt ursprungliga tillstånd, bör du överväga TRUNCATE.

Många människor blandar ihop DELETE och TRUNCATE. Om du är osäker rekommenderar jag att du läser mitt inlägg What’s the Difference between Truncate and Delete in SQL Server?

Complex Example – DELETE Using a SubQuery

Du kan också skapa mer komplexa delete-statement. Nästan vilken klausul som helst som du kan skriva in i en WHERE-klausul i ett SELECT-uttalande kan skrivas in i DELETE-uttalanden, inklusive underfrågor i WHERE-klausulen.

Låt oss ta ett exempel.

Antag att du behöver ta bort alla säljare som bor i USA från tabellen esqlSalesPerson. Även om vår tabell har City har den inte land. Vi kan komma runt detta genom att använda en subquery.

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

Bemärk att jag lindade in exemplet i en transaktion så att jag inte permanent skulle radera mina testdata.

Den subquery är färgad blå. Lägg märke till att den inte skiljer sig från någon annan korrelerad underfråga som du kan ha använt tidigare.

Mekaniken i denna DELETE-anvisning är:

1. Sök efter distinkta städer för alla personer som bor i USA.
2. DELETE försäljningspersoner vars stad finns i den här listan.

Det första steget resulterar av underfrågelistan. Det andra steget tar bort rader enligt WHERE-klausulen.