Johdatus Delete-lauseeseen

DELETE-lauseen avulla poistetaan rivejä SQL Serverin datataulusta. Tässä artikkelissa tutustumme DELETE-lauseen käyttöön. Keskustelemme joistakin parhaista käytännöistä, rajoituksista ja käärimme lopuksi useita esimerkkejä.

Tämä on neljäs artikkeli artikkelisarjassa. Voit aloittaa alusta lukemalla Johdatus SQL Serverin tietojen muutoslausekkeisiin.

Kaikki tämän oppitunnin esimerkit perustuvat Microsoft SQL Server Management Studioon ja AdventureWorks2012-tietokantaan. Voit aloittaa näiden ilmaisten työkalujen käytön käyttämällä opastani Getting Started Using SQL Server

Before we Begin

Vaikka tässä artikkelissa käytetään AdventureWorks-tietokantaa esimerkkeihin, olen päättänyt luoda esimerkkitaulukon käytettäväksi tietokannassa, jotta esimerkkejä voidaan havainnollistaa paremmin. Löydät ajettavaksi tarvittavan skriptin täältä.

Täytetään taulukkoon aluksi myös joitakin tietoja seuraavan INSERT-lauseen avulla:

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

Lue lisää INSERT-lauseesta artikkelista Johdatus INSERT-lauseeseen.

DELETE-lausekkeen perusrakenne

DELETE-lauseketta käytetään sarakearvojen muuttamiseen.

DELETE-lausekkeessa on kolme komponenttia:

1. Taulukko, josta haluat poistaa rivejä.
2. Kriteerit, joita käytetään poistettavien rivien valitsemiseen.

DELECT-lauseen yleinen muoto on:

DELETEFROM tableNameWHERE searchCondition…

Tehdään nyt muutama DELETE-esimerkki, joten jos et ole vielä tehnyt sitä, suorita skripti, jolla luodaan esqlSalesPerson-taulu.

Yksinkertainen esimerkki – jokaisen rivin poistaminen

Kun käytät DELETEa ilman WHERE-lauseketta, se poistaa jokaisen rivin taulukosta. Jos haluamme poistaa jokaisen esqlSalesPerson-tietokannasta, voisimme suorittaa:

DELETEFROM esqlSalesPerson

Suosittelen esimerkkimme vuoksi kietomaan DELETE-komennon transaktioon, jotta sinun ei tarvitse lisätä toistuvasti rivejä esimerkkitietokantaan. Tässä näet saman DELETE-käskyn.

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

Ensimmäinen count-käsky palauttaa 13 riviä; kun taas toinen palauttaa 0. Koska ROLLBACK-transaktiota, poisto-operaatio ei ole pysyvä.

Yksinkertainen esimerkki – Yksittäisen rivin poistaminen

Realistisempi käyttötapaus on yksittäisen rivin poistaminen. Monissa sovelluksissa tämä saavutetaan suodattamalla yksittäinen rivi.

Jos tunnet ensisijaisen avaimen, olet hyvässä asemassa, sillä ensisijainen avain on yksilöllinen ja sen tarkoitus on tunnistaa jokainen rivi.

Asettele, että haluamme poistaa Jillian Carsonin rivin. Tehdäksemme niin antaisimme seuraavan ohjeen:

DELETEFROM esqlSalesPersonWHERE SalesPersonID = 10095

Voit ehkä ihmetellä, mistä tiedät ensisijaisen avaimen.

Voit kuvitella, että jos sinulla olisi web-sovellus, jossa lueteltaisiin jokainen myyntihenkilö, että ruudukko saattaisi sisältää myyntihenkilön koko nimen ja viime vuoden liikevaihdon, mutta ruudukkoon piilotettuna olisi myös myyntihenkilön SalesPersonID.

Kun käyttäjä valitsee ruudukon rivin ja päättää poistaa rivin, sovellus hakisi rivin piilotetun ensisijaisen avaimen ja antaisi sitten tietokantaan poistokomennon.

Yksinkertainen esimerkki – Useiden rivien poistaminen

Esitettäisiin, että esqlSalesPerson-taulukossa haluttaisiin näyttää vain hyvin suoriutuvat myyjät. Haluamme pitää vain ne myyntihenkilöt, joiden viime vuoden myynti on vähintään 2 000 000 dollaria.

Koska taulukkomme sisältää niitä, joiden myynti on alle tämän summan, meidän on poistettava heidät suorittamalla seuraava:

DELETEFROM esqlSalesPersonWHERE SalesLastYear > 2000000.00

Voit kokeilla skriptiä transaktion sisällä seuraavalla komennolla:

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

Tässä on skriptin suorittamisen tulos:

Sininen nuoli osoittaa, että alun perin tietueita oli 13, ja punainen jäljelle jääneet, joiden liikevaihto on yli tai yhtä suuri kuin kaksi miljoonaa dollaria.

Voit luoda myös monimutkaisempia suodatusehtoja. Myöhemmin artikkelissa näytetään, miten alakyselyä käytetään. Voit myös käyttää Boolean-ehtoja, WHERE-lausekkeessa samoin kuin SELECT-lausekkeessa.

Huomioita DELETE-lausekkeen käytöstä

Taulukon kaikkien rivien poistamiseen käytetään TRUNCATE TABLE -ohjelmaa. Se on paljon nopeampi kuin DELETE, koska se kirjaa muutokset. Siinä on kuitenkin keskeisiä eroja (katso DELETE isn’t TRUNCATE! alla)

Älä myöskään unohda, että voit käyttää @@ROWCOUNT-funktiota saadaksesi selville, kuinka monta riviä poistettiin.

Virheiden käsittely

Jos DELETE-lause heittää virheen, kaikki rivit palautetaan tilaan, jossa ne olivat ennen lausekkeen suorittamista. Jos virhe laukeaa, rivejä ei poisteta.

Poistolauseen epäonnistumiseen voi olla monia syitä. Tyypillisimpiä niistä ovat:

• Orpoja rivejä – jos kahden taulun, kuten parentTable ja childTable, välille on määritelty FOREIGN KEY -rajoitus, parentTable-rivin DELETE aiheuttaa virheen, jos parentTable-riviin liittyviä childTable-rivejä on olemassa. Tämä voidaan kiertää poistamalla ensin vastaavat childTable-rivit ja sitten parentTable-rivi.
• Aritmeettiset virheet – Jos lausekkeen evaluointi johtaa aritmeettiseen virheeseen, kuten jakamiseen nollalla, DELETE peruutetaan eikä rivejä poisteta.

Lukituskäyttäytyminen

Poistamislauseke asettaa taululle eksklusiivisen (X) lukituksen. Tämä tarkoittaa, että mikään muu kysely ei voi muuttaa taulukon tietoja, ennen kuin DELETE-transaktio on valmis.

Voit silti lukea tietoja, mutta sinun on käytettävä NOLOCK-vihjettä tai read uncommitted -eristystasoa.

DELETE isn’t TRUNCATE!

Delete isn’t TRUNCATE! Käytä DELETEa yhden tai useamman rivin poistamiseen taulusta. Vain erityistilanteissa, kuten silloin, kun taulukko on palautettava alkutilaansa, kannattaa harkita TRUNCATEa.

Monet sekoittavat DELETEn ja TRUNCATEn. JOS olet epävarma, suosittelen lukemaan kirjoitukseni Mitä eroa on Truncate- ja Delete-lausekkeilla SQL Serverissä?

Kompleksinen esimerkki – DELETE käyttämällä alakyselyä

Voit luoda myös monimutkaisempia delete-lausekkeita. Melkein mikä tahansa lauseke, jonka voit kirjoittaa SELECT-lauseen WHERE-lausekkeeseen, voidaan kirjoittaa DELETE-lauseeseen, mukaan lukien WHERE-lausekkeessa olevat alikyselyt.

Tehdään esimerkki.

Esitellään, että sinun on poistettava kaikki Yhdysvalloissa asuvat myyntihenkilöt esqlSalesPerson-taulusta. Vaikka taulussamme on kaupunki, siinä ei ole maata. Voimme kiertää tämän käyttämällä alakyselyä.

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

Huomaa, että käärin esimerkin transaktioon, jotta en poistaisi testitietojani pysyvästi.

Alikysely on väritetty sinisellä. Huomaa, että se ei eroa mistään muusta aiemmin käyttämästäsi korreloidusta alakyselystä.

Tämän DELETE-lauseen mekaniikka on seuraava:

1. Etsitään erilliset kaupungit kaikille henkilöille, jotka asuvat USA:ssa.
2. DELETE-myyntihenkilöt, joiden kaupunki on tässä luettelossa.

Ensimmäisen askeleen tuloksena syntyy alakysely. Toinen vaihe poistaa WHERE-lausekkeen mukaiset rivit.