Příkaz DELETE slouží k odstranění řádků z datové tabulky SQL Serveru. V tomto článku se budeme zabývat používáním příkazu DELETE. Probereme některé osvědčené postupy, omezení a na závěr uvedeme několik příkladů.
Tento článek je čtvrtým ze série článků. Můžete začít od začátku přečtením článku Úvod do příkazů pro úpravu dat SQL Serveru.
Všechny příklady pro tuto lekci jsou založeny na Microsoft SQL Server Management Studio a databázi AdventureWorks2012. S těmito bezplatnými nástroji můžete začít pracovat pomocí mé příručky Začínáme používat SQL Server
Než začneme
Přestože tento článek používá pro své příklady databázi AdventureWorks, rozhodl jsem se vytvořit příkladovou tabulku pro použití v databázi, aby bylo možné příklady lépe ilustrovat. Skript, který budete potřebovat ke spuštění, najdete zde.
Na začátku naplníme tabulku daty pomocí následujícího příkazu INSERT:
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
Více informací o příkazu INSERT se dozvíte v článku Úvod do příkazu INSERT.
Základní struktura příkazu DELETE
Příkaz DELETE slouží ke změně hodnot sloupců.
Příkaz DELETE má tři součásti:
- Tabulka, ze které chcete odstranit řádky.
- Kritéria použitá pro výběr řádků k odstranění.
Obecný formát příkazu DELECT je:
DELETEFROM tableNameWHERE searchCondition…
Nyní provedeme několik ukázkových příkazů DELETE, takže pokud jste tak ještě neučinili, spusťte skript pro vytvoření tabulky esqlSalesPerson.
Ukázkový příklad – odstranění každého řádku
Při použití DELETE bez klauzule WHERE dojde k odstranění každého řádku z tabulky. Pokud bychom chtěli odstranit každý z esqlSalesPerson, mohli bychom spustit:
DELETEFROM esqlSalesPerson
Pro účely našeho příkladu bych doporučoval zabalit příkaz DELETE do transakce, abyste nemuseli opakovaně vkládat řádky do databáze příkladu. Zde uvidíte stejný příkaz DELETE.
BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonSELECT COUNT(1) FROM esqlSalesPersonROLLBACK
První příkaz počítání vrátí 13 řádků; zatímco druhý vrátí 0. Protože transakci vrátím zpět, operace smazání není trvalá.
Jednoduchý příklad – smazání jednoho řádku
Reálnější případ použití je smazání jednoho řádku. V mnoha aplikacích je toho dosaženo filtrováním na jediném řádku.
Pokud znáte primární klíč, máte vyhráno, protože primární klíč je jedinečný a má pozitivně identifikovat každý řádek.
Předpokládejme, že chceme odstranit řádek Jillian Carson. K tomu bychom vydali následující příkaz:
DELETEFROM esqlSalesPersonWHERE SalesPersonID = 10095
Možná si říkáte, jak byste poznali primární klíč.
Můžete si představit, že kdybyste měli webovou aplikaci, která by vypisovala všechny prodejce, že by mřížka mohla obsahovat celé jméno prodejce a jeho tržby za poslední rok, ale v mřížce by se také skrývalo jeho SalesPersonID.
Když by uživatel vybral řádek v mřížce a rozhodl se jej odstranit, aplikace by načetla skrytý primární klíč pro tento řádek a poté by vydala příkaz k odstranění do databáze.
Jednoduchý příklad – odstranění více řádků
Předpokládejme, že chceme v tabulce esqlSalesPerson zobrazit pouze vysoce výkonné prodejce. Chceme si ponechat pouze ty prodejce, jejichž loňské tržby byly větší nebo rovny 2 000 000 USD.
Protože naše tabulka obsahuje ty, jejichž tržby jsou menší než tato částka, musíme je odstranit spuštěním následujícího příkazu:
DELETEFROM esqlSalesPersonWHERE SalesLastYear > 2000000.00
Pro vyzkoušení skriptu v rámci transakce můžete spustit následující příkaz:
BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonWHERE SalesLastYear < 2000000.00SELECT FullName, SalesLastYearFROM esqlSalesPersonROLLBACK
Tady je výsledek spuštění skriptu:
Modrá šipka ukazuje, že původně bylo 13 záznamů a červená zbývající, jejichž tržby jsou větší nebo rovny dvěma milionům dolarů.
Můžete také vytvořit složitější podmínky filtrování. Později v článku si ukážeme, jak použít poddotaz. Můžete také použít logické podmínky, a to v klauzuli WHERE stejně jako v případě příkazu SELECT.
Použití příkazu DELETE
Chcete-li odstranit všechny řádky v tabulce, použijte příkaz TRUNCATE TABLE. Je mnohem rychlejší než DELETE, protože zaznamenává změny. Existují však zásadní rozdíly (viz DELETE není TRUNCATE! níže)
Nezapomeňte také, že můžete použít funkci @@ROWCOUNT, abyste zjistili, kolik řádků bylo smazáno.
Ošetřování chyb
Pokud příkaz DELETE vyhodí chybu, všechny řádky se obnoví do stavu před spuštěním příkazu. Pokud je vyvolána chyba, nejsou odstraněny žádné řádky.
Příkaz delete může selhat z mnoha důvodů. Mezi nejtypičtější patří:
- Osiřelé řádky – pokud je mezi dvěma tabulkami, například parentTable a childTable, definováno omezení FOREIGN KEY, pak DELETE řádku parentTable způsobí chybu, pokud existují řádky childTable související s rodičem. Obejde se to tak, že se nejprve odstraní odpovídající řádky podřízenéTabulky a pak řádek nadřízenéTabulky.
- Aritmetické chyby – pokud vyhodnocení výrazu vede k aritmetické chybě, například dělení nulou, DELETE se zruší a žádné řádky se neodstraní.
Chování při zamykání
Příkaz delete umístí exkluzivní (X) zámek na tabulku. To znamená, že žádný jiný dotaz nemůže měnit data tabulky, dokud není transakce DELETE dokončena.
Data můžete stále číst, ale musíte použít nápovědu NOLOCK nebo úroveň izolace read uncommitted.
DELETE není TRUNCATE!
Delete není TRUNCATE! K odstranění jednoho nebo více řádků z tabulky použijte DELETE. Pouze ve zvláštní situaci, například když potřebujete uvést tabulku do původního stavu, byste měli uvažovat o TRUNCATE.
Mnoho lidí si plete DELETE a TRUNCATE. POKUD si nejste jisti, doporučuji přečíst si můj příspěvek na téma Jaký je rozdíl mezi Truncate a Delete v SQL Serveru
Složitý příklad – DELETE pomocí poddotazu
Můžete také vytvořit složitější příkaz delete. Téměř jakoukoli klauzuli, kterou můžete zapsat do klauzule WHERE příkazu SELECT, můžete zapsat do příkazu DELETE’s, včetně poddotazů v klauzuli WHERE.
Uveďme si příklad.
Předpokládejme, že potřebujete z tabulky esqlSalesPerson odstranit všechny prodejce, kteří žijí v USA. Naše tabulka sice obsahuje město, ale neobsahuje zemi. To můžeme obejít pomocí poddotazu.
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
Všimněte si, že jsem příklad zabalil do transakce, abych trvale neodstranil svá testovací data.
Poddotaz je podbarven modře. Všimněte si, že se nijak neliší od jiných korelovaných poddotazů, které jste možná použili v minulosti.
Mechanika tohoto příkazu DELETE je následující:
- Vyhledejte různá města pro všechny lidi, kteří žijí v USA.
- DELETE prodejce, jejichž město je v tomto seznamu.
První krok je výsledkem poddotazu. Druhý krok odstraní řádky podle klauzule WHERE.