A Delete utasítás bemutatása

A DELETE utasítás sorok eltávolítására szolgál az SQL Server adattáblájából. Ebben a cikkben a DELETE utasítás használatát vizsgáljuk meg. Megbeszélünk néhány bevált gyakorlatot, korlátozást, és néhány példával zárjuk a témát.

Ez a negyedik cikk a cikksorozatban. Kezdheti az elején a Bevezetés az SQL Server adatmódosító utasításaiba című részt.

A lecke összes példája a Microsoft SQL Server Management Studio és az AdventureWorks2012 adatbázisán alapul. Ezeknek az ingyenes eszközöknek a használatát az Útmutató az SQL Server használatához

Before we Begin

Noha ez a cikk az AdventureWorks adatbázist használja a példákhoz, úgy döntöttem, hogy a példák jobb szemléltetése érdekében létrehozok egy példatáblát az adatbázisban való használatra. A futtatáshoz szükséges szkriptet itt találja meg.

A táblát kezdetben töltsük fel néhány adattal a következő INSERT utasítással:

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

Az INSERT utasításról többet megtudhat a Bevezetés az INSERT utasításba című cikkünkből.

A DELETE utasítás alapvető felépítése

A DELETE utasítás az oszlopértékek megváltoztatására szolgál.

A DELETE utasításnak három összetevője van:

1. A táblázat, amelyből sorokat szeretnénk eltávolítani.
2. Az eltávolítandó sorok kiválasztásához használt kritériumok.

A DELECT utasítás általános formátuma:

DELETEFROM tableNameWHERE searchCondition…

Most néhány minta DELETE-t fogunk végrehajtani, ezért ha még nem tette meg, futtassa le a szkriptet az esqlSalesPerson tábla létrehozásához.

Egyszerű példa – Minden sor törlése

Ha a DELETE-t WHERE záradék nélkül használjuk, akkor minden sort eltávolít a táblázatból. Ha minden sort törölni szeretnénk az esqlSalesPersonból, akkor a következőt futtathatjuk:

DELETEFROM esqlSalesPerson

Példánk szempontjából azt javasolnám, hogy a DELETE parancsot csomagoljuk tranzakcióba, hogy ne kelljen többször sorokat beszúrni a példa adatbázisába. Itt ugyanazt a DELETE utasítást látja.

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

Az első számláló utasítás 13 sort ad vissza; míg a második 0-t. Mivel ROLLBACK a tranzakciót, a törlési művelet nem végleges.

Egyszerű példa – Egyetlen sor törlése

Egy reálisabb felhasználási eset egyetlen sor törlése. Sok alkalmazásban ez egyetlen sor szűrésével érhető el.

Ha ismerjük az elsődleges kulcsot, akkor jól járunk, mivel az elsődleges kulcs egyedi és arra szolgál, hogy minden sort egyértelműen azonosítson.

Tegyük fel, hogy törölni szeretnénk Jillian Carson sorát. Ehhez a következőket adnánk ki:

DELETEFROM esqlSalesPersonWHERE SalesPersonID = 10095

Elgondolkodhat azon, hogy honnan ismeri az elsődleges kulcsot.

Elképzelheti, hogy ha lenne egy webes alkalmazásunk, amely minden értékesítő személyt felsorol, akkor a rácson szerepelhetne az értékesítő teljes neve és tavalyi árbevétele, de a rácson rejtve lenne a SalesPersonID is.

Amikor a felhasználó kiválaszt egy sort a rácson, és úgy dönt, hogy eltávolítja a sort, az alkalmazás lekérdezné a sor rejtett elsődleges kulcsát, majd kiadná a törlési parancsot az adatbázisnak.

Egyszerű példa – Több sor törlése

Tegyük fel, hogy csak a jól teljesítő értékesítőket szeretnénk megjeleníteni az esqlSalesPerson táblában. Csak azokat az értékesítőket szeretnénk megtartani, akiknek a tavalyi árbevétele nagyobb vagy egyenlő 2 000 000 dollárral.

Mivel a táblázatunk tartalmazza azokat, akiknek az árbevétele kisebb ennél az összegnél, a következők futtatásával kell őket eltávolítanunk:

DELETEFROM esqlSalesPersonWHERE SalesLastYear > 2000000.00

A szkriptet egy tranzakción belül a következő paranccsal próbálhatjuk ki:

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

Íme a szkript futtatásának eredménye:

A kék nyíl mutatja, hogy eredetileg 13 rekord volt, a piros pedig a maradék, amelynek az árbevétele nagyobb vagy egyenlő kétmillió dollárral.

Elkészíthet összetettebb szűrési feltételeket is. A cikk későbbi részében megmutatjuk, hogyan használhatunk alkérdést. Boolean feltételeket is használhatunk, a WHERE záradékban ugyanúgy, mint a SELECT utasításnál.

Figyelmeztetések a DELETE utasítás használatával

A táblázat összes sorának törléséhez használjuk a TRUNCATE TABLE parancsot. Sokkal gyorsabb, mint a DELETE, mivel naplózza a változásokat. De vannak lényeges különbségek (lásd alább: A DELETE nem TRUNCATE!)

Az @@ROWCOUNT függvény segítségével megtudhatjuk, hány sort töröltünk.

Hibakezelés

Ha a DELETE utasítás hibát dob, minden sor visszaáll az utasítás futtatása előtti állapotába. Hiba esetén egyetlen sor sem kerül eltávolításra.

Egy delete utasítás sikertelenségének számos oka lehet. A tipikusabbak közül néhány:

• Váratlan sorok – ha két tábla, például a parentTable és a childTable között FOREIGN KEY megkötés van definiálva, akkor a parentTable sorának DELETE hibát okoz, ha a parentTable-hez kapcsolódó childTable sorok léteznek. Ezt úgy lehet megkerülni, hogy először a megfelelő childTable sorokat, majd a parentTable sort távolítja el.
• Aritmetikai hibák – Ha egy kifejezés kiértékelése aritmetikai hibát eredményez, például osztás nullával, a DELETE törlődik, és nem távolít el sorokat.

Locking Behavior

A delete utasítás kizárólagos (X) zárat helyez a táblára. Ez azt jelenti, hogy semmilyen más lekérdezés nem módosíthatja a tábla adatait, amíg a DELETE tranzakció be nem fejeződik.

Az adatokat továbbra is olvashatja, de használnia kell a NOLOCK súgót vagy a read uncommitted izolációs szintet.

A DELETE nem TRUNCATE!

A DELETE nem TRUNCATE! A DELETE használatával egy vagy több sort távolíthat el egy táblából. Csak különleges helyzetekben, például amikor egy táblát vissza kell állítani a kezdeti állapotába, érdemes megfontolni a TRUNCATE-t.

Mi sokan összekeverik a DELETE és a TRUNCATE-t. HA bizonytalan, akkor ajánlom, hogy olvassa el a Mi a különbség a Truncate és a Delete között az SQL Serverben című bejegyzésemet.

Bonyolult példa – DELETE egy alkérdés használatával

Elég összetettebb delete utasítást is létrehozhat. A SELECT utasítás WHERE záradékába írható szinte bármilyen záradék beleírható a DELETE utasításba, beleértve a WHERE záradékban szereplő alkérdéseket is.

Mutatunk egy példát.

Tegyük fel, hogy az esqlSalesPerson táblából el kell távolítani az összes olyan értékesítő személyt, aki az Egyesült Államokban él. Bár a táblázatunkban van City, de nincs benne ország. Ezt egy alkérdéssel megkerülhetjük.

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

Kérem, vegye figyelembe, hogy a példát egy tranzakcióba csomagoltam, hogy ne töröljem véglegesen a tesztadataimat.

Az alkérdés kék színű. Vegye észre, hogy nem különbözik bármely más korrelált alkérdéstől, amelyet a múltban használhatott.

A DELETE utasítás mechanikája a következő:

1. Keresd meg az USA-ban élő összes ember különálló városát.
2. DELETE értékesítési emberek, akiknek a városa szerepel ebben a listában.

Az első lépés eredménye az alkérdés. A második lépés a WHERE záradék szerinti sorokat törli.