Introduktion til Delete-erklæringen

Den DELETE-erklæring bruges til at fjerne rækker fra en SQL Server-datatatabel. I denne artikel vil vi undersøge, hvordan man bruger DELETE-erklæringen. Vi diskuterer nogle bedste praksis, begrænsninger og afslutter med flere eksempler.

Dette er den fjerde artikel i en serie af artikler. Du kan starte i begyndelsen ved at læse Introduktion til SQL Server Data Modification Statements.

Alle eksempler i denne lektion er baseret på Microsoft SQL Server Management Studio og AdventureWorks2012-databasen. Du kan komme i gang med at bruge disse gratis værktøjer ved hjælp af min vejledning Kom godt i gang med SQL Server

Hvor vi begynder

Selv om denne artikel bruger AdventureWorks-databasen til sine eksempler, har jeg besluttet at oprette en eksempeltabel til brug i databasen for bedre at illustrere eksemplerne. Du kan finde det script, du skal køre, her.

Lad os også i første omgang fylde tabellen med nogle data ved hjælp af følgende 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ære mere om INSERT-anvisningen ved at læse vores artikel Introduktion til INSERT-anvisningen.

Grundlæggende opbygning af DELETE-erklæringen

Den DELETE-erklæring bruges til at ændre kolonneværdier.

Der er tre komponenter i en DELETE-erklæring:

1. Tabellen, som du ønsker at fjerne rækker fra.
2. Kriterierne, der bruges til at vælge de rækker, der skal fjernes.

Det generelle format for DELECT-erklæringen er:

DELETEFROM tableNameWHERE searchCondition…

Vi skal nu lave nogle eksempler på DELETE-erklæringer, så hvis du ikke allerede har gjort det, skal du køre scriptet for at oprette tabellen esqlSalesPerson.

Enkel eksempel – sletning af hver række

Når du bruger DELETE uden en WHERE-klausul, fjernes alle rækker fra tabellen. Hvis vi ønsker at slette alle fra esqlSalesPerson, kan vi køre:

DELETEFROM esqlSalesPerson

Jeg vil anbefale, med henblik på vores eksempel, at indpakke DELETE-kommandoen i en transaktion, så du ikke behøver at indsætte rækker gentagne gange i eksempeldatabasen. Her ser du den samme DELETE-anvisning.

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

Den første tælleanvisning returnerer 13 rækker, mens den anden returnerer 0. Da jeg ROLLBACKer transaktionen, er sletteoperationen ikke permanent.

Enkel eksempel – sletning af en enkelt række

En mere realistisk brugssituation det at slette en enkelt række. I mange applikationer opnås dette ved at filtrere på en enkelt række.

Hvis du kender primærnøglen, er du godt stillet, da primærnøglen er unik og beregnet til at identificere hver enkelt række.

Sæt, at vi ønsker at slette Jillian Carsons række. For at gøre det ville vi udstede følgende:

DELETEFROM esqlSalesPersonWHERE SalesPersonID = 10095

Du undrer dig måske over, hvordan du kender primærnøglen.

Du kan forestille dig, at hvis du havde en web-app, der listede alle salgspersoner, at gitteret kan indeholde sælgerens fulde navn og sidste års salg, men skjult på gitteret ville også være deres SalesPersonID.

Når en bruger valgte en række på gitteret og valgte at fjerne rækken, ville programmet hente den skjulte primærnøgle for rækken og derefter udstede slettekommandoen til databasen.

Enkel eksempel – sletning af flere rækker

Sæt, at vi kun ønsker at vise højtydende sælgere i tabellen esqlSalesPerson. Vi ønsker kun at beholde de sælgere, hvis salg sidste år var større end eller lig med 2.000.000 dollars.

Da vores tabel indeholder dem med mindre end dette beløb, skal vi fjerne dem ved at køre følgende:

DELETEFROM esqlSalesPersonWHERE SalesLastYear > 2000000.00

Du kan køre følgende kommando for at prøve scriptet inden for en transaktion:

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

Her er resultatet af at køre scriptet:

Den blå pil viser, at der oprindeligt var 13 poster, og den røde de resterende, hvis salg er større end eller lig med to millioner dollars.

Du kan også oprette mere komplekse filtreringsbetingelser. Senere i artiklen viser vi, hvordan du bruger en subquery. Du kan også bruge boolske betingelser, også i WHERE-klausulen, ligesom du ville gøre det med SELECT-erklæringen.

Overvejelser ved hjælp af DELETE-erklæringen

For at slette alle rækker i en tabel skal du bruge TRUNCATE TABLE. Det er meget hurtigere end DELETE, da det logger ændringer. Men der er vigtige forskelle (se DELETE er ikke TRUNCATE! nedenfor)

Glem ikke, at du også kan bruge @@ROWCOUNT-funktionen til at finde ud af, hvor mange rækker der blev slettet.

Fejlehåndtering

Hvis en DELETE-erklæring kaster en fejl, gendannes alle rækker til deres tilstand, før erklæringen blev kørt. Hvis der udløses en fejl, fjernes ingen rækker.

Der er mange grunde til, at en delete-anvisning kan mislykkes. Nogle af de mere typiske omfatter:

• Orphan Rows – hvis der er defineret en FOREIGN KEY-begrænsning mellem to tabeller, f.eks. parentTable og childTable, vil DELETE en overordnet række i parentTable forårsage en fejl, hvis der findes childTable-rækker, der er relateret til den overordnede. Dette kan omgås ved først at fjerne de tilsvarende childTable-rækker og derefter parentTable-rækken.
• Aritmetiske fejl – Hvis en evaluering af et udtryk resulterer i en aritmetisk fejl, f.eks. divideret med nul, annulleres DELETE, og ingen rækker fjernes.

Låsningsadfærd

En delete-anvisning placerer en eksklusiv (X) lås på tabellen. Det betyder, at ingen andre forespørgsler kan ændre tabellens data, før DELETE-transaktionen er afsluttet.

Du kan stadig læse data, men du skal bruge NOLOCK-hinvisningen eller læse ukommitteret isolationsniveau.

DELETE er ikke TRUNCATE!

Delete er ikke TRUNCATE! Brug DELETE til at fjerne en eller flere rækker fra en tabel. Kun i særlige situationer, f.eks. når du har brug for at nulstille en tabel til dens oprindelige tilstand, bør du overveje TRUNCATE.

Mange mennesker blander DELETE og TRUNCATE sammen. HVIS du er i tvivl, vil jeg anbefale dig at læse mit indlæg om Hvad er forskellen mellem Truncate og Delete i SQL Server?

Komplekst eksempel – DELETE ved hjælp af en SubQuery

Du kan også oprette mere komplekse delete-statement. Stort set alle klausuler, du kan skrive i en SELECT statement WHERE-klausul, kan skrives i DELETE statement’s, herunder subqueries i WHERE-klausulen.

Lad os lave et eksempel.

Sæt, du skal fjerne alle salgspersoner, der bor i USA, fra tabellen esqlSalesPerson. Selv om vores tabel har City, har den ikke land. Det kan vi komme udenom ved at bruge en underafspørgsel.

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, at jeg pakkede eksemplet ind i en transaktion, så jeg ikke permanent ville slette mine testdata.

Underafspørgslen er farvet blå. Bemærk, at den ikke adskiller sig fra andre korrelerede underafspørgsler, som du måske tidligere har brugt.

Mekanikken i denne DELETE-anvisning er:

1. Find forskellige byer for alle personer, der bor i USA.
2. DELETE salgspersoner, hvis by er på denne liste.

Det første trin er resultatet af underafspørgslen. Det andet trin sletter rækker i henhold til WHERE-klausulen.