Introducere în instrucțiunea Delete

Instrucțiunea DELETE este utilizată pentru a elimina rânduri dintr-un tabel de date SQL Server. În acest articol vom explora modul de utilizare a instrucțiunii DELETE. Discutăm câteva bune practici, limitări și încheiem cu câteva exemple.

Acesta este al patrulea articol dintr-o serie de articole. Puteți începe de la început citind Introducere în declarațiile de modificare a datelor SQL Server.

Toate exemplele pentru această lecție se bazează pe Microsoft SQL Server Management Studio și pe baza de date AdventureWorks2012. Puteți începe să folosiți aceste instrumente gratuite folosind Ghidul meu Getting Started Using SQL Server

Până la început

Deși acest articol folosește baza de date AdventureWorks pentru exemplele sale, am decis să creez o tabelă de exemplu pentru utilizare în cadrul bazei de date pentru a ajuta la o mai bună ilustrare a exemplelor. Puteți găsi scriptul pe care va trebui să îl executați aici.

De asemenea, să populăm inițial tabelul cu câteva date folosind următoarea instrucțiune 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

Puteți afla mai multe despre instrucțiunea INSERT citind articolul nostru Introducere în instrucțiunea INSERT.

Structura de bază a instrucțiunii DELETE

Instrucțiunea DELETE este utilizată pentru a schimba valorile coloanelor.

Există trei componente ale unei instrucțiuni DELETE:

1. Tabela din care doriți să eliminați rânduri.
2. Criteriile folosite pentru a alege rândurile care vor fi eliminate.

Formul general al declarației DELECT este:

DELETEFROM tableNameWHERE searchCondition…

Acum vom face câteva exemple de DELETE, așa că, dacă nu ați făcut-o deja, rulați scriptul pentru a crea tabelul esqlSalesPerson.

Exemplu simplu – Ștergerea fiecărui rând

Când folosiți DELETE fără o clauză WHERE, se elimină fiecare rând din tabel. Dacă dorim să ștergem fiecare din esqlSalesPerson am putea rula:

DELETEFROM esqlSalesPerson

Am recomanda, în scopul exemplului nostru, să înglobăm comanda DELETE într-o tranzacție pentru a nu fi nevoiți să introduceți în mod repetat rânduri în baza de date de exemplu. Aici veți vedea aceeași instrucțiune DELETE.

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

Prima instrucțiune de numărare returnează 13 rânduri; în timp ce, a doua returnează 0. Deoarece am ROLLBACK tranzacția, operațiunea de ștergere nu este permanentă.

Exemplu simplu – Ștergerea unui singur rând

Un caz de utilizare mai realist este ștergerea unui singur rând. În multe aplicații, acest lucru se realizează prin filtrarea pe un singur rând.

Dacă știți cheia primară, sunteți de aur, deoarece cheia primară este unică și este menită să identifice în mod pozitiv fiecare rând.

Să presupunem că dorim să ștergem rândul lui Jillian Carson. Pentru a face acest lucru, am emite următoarele:

DELETEFROM esqlSalesPersonWHERE SalesPersonID = 10095

S-ar putea să vă întrebați cum ați putea ști cheia primară.

Vă puteți imagina dacă ați avea o aplicație web care listează fiecare persoană de vânzări, că grila poate conține numele complet al persoanei de vânzări și vânzările din ultimul an, dar ascuns în grilă ar fi, de asemenea, SalesPersonID.

Când un utilizator a selectat un rând din grilă și a ales să elimine rândul, aplicația ar prelua cheia primară ascunsă pentru rândul respectiv și apoi ar emite comanda de ștergere către baza de date.

Exemplu simplu – Ștergerea mai multor rânduri

Să presupunem că dorim să afișăm doar persoanele de vânzări cu performanțe ridicate în tabelul esqlSalesPerson. Vrem să păstrăm doar acei oameni de vânzări cu vânzări de anul trecut mai mari sau egale cu 2.000.000 $.

Din moment ce tabelul nostru îi conține pe cei care au mai puțin de această sumă, trebuie să îi eliminăm prin rularea următoarelor:

DELETEFROM esqlSalesPersonWHERE SalesLastYear > 2000000.00

Puteți rula următoarea comandă pentru a încerca scriptul în cadrul unei tranzacții:

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

Iată rezultatul rulării scriptului:

Săgeata albastră arată că au existat inițial 13 înregistrări, iar cea roșie pe cele rămase ale căror vânzări sunt mai mari sau egale cu două milioane de dolari.

De asemenea, puteți crea condiții de filtrare mai complexe. Mai târziu în articol vom arăta cum să folosim o subinterogare. De asemenea, puteți utiliza condiții booleene, în clauza WHERE, la fel ca și în cazul instrucțiunii SELECT.

Considerații privind utilizarea instrucțiunii DELETE

Pentru a șterge toate rândurile dintr-un tabel, utilizați TRUNCATE TABLE. Este mult mai rapid decât DELETE, deoarece înregistrează modificările. Dar există diferențe esențiale (a se vedea DELETE nu este TRUNCATE! mai jos)

De asemenea, nu uitați că puteți utiliza funcția @@ROWCOUNT pentru a afla câte rânduri au fost șterse.

Manipularea erorilor

Dacă o instrucțiune DELETE aruncă o eroare, toate rândurile sunt readuse la starea în care se aflau înainte ca instrucțiunea să fie executată. Dacă se declanșează o eroare, niciun rând nu este eliminat.

Există multe motive pentru care o instrucțiune de ștergere poate eșua. Unele dintre cele mai tipice includ:

• Rânduri orfane – dacă este definită o constrângere FOREIGN KEY între două tabele, cum ar fi parentTable și childTable, atunci DELETE un rând din parentTable va cauza o eroare dacă există rânduri din childTable legate de părinte. O modalitate de a evita acest lucru este de a elimina mai întâi rândurile childTable corespunzătoare, apoi rândul parentTable.
• Erorile aritmetice – Dacă evaluarea unei expresii are ca rezultat o eroare aritmetică, cum ar fi împărțirea la zero, DELETE este anulată și nu se elimină niciun rând.

Comportamentul de blocare

O instrucțiune de ștergere plasează o blocare exclusivă (X) pe tabel. Acest lucru înseamnă că nicio altă interogare nu poate modifica datele tabelului până la finalizarea tranzacției DELETE.

Puteți citi în continuare datele, dar trebuie să folosiți indiciul NOLOCK sau nivelul de izolare read uncommitted.

DELETE nu este TRUNCATE!

Delete nu este TRUNCATE! Utilizați DELETE pentru a elimina unul sau mai multe rânduri dintr-un tabel. Numai în situații speciale, cum ar fi atunci când trebuie să readuceți un tabel la starea sa inițială, ar trebui să luați în considerare TRUNCATE.

Mulți oameni confundă DELETE și TRUNCATE. DACĂ nu sunteți siguri, v-aș recomanda să citiți postarea mea cu privire la Care este diferența dintre Truncate și Delete în SQL Server?

Exemplu complex – DELETE folosind un SubQuery

De asemenea, puteți crea instrucțiuni de ștergere mai complexe. Aproape orice clauză pe care o puteți scrie în clauza WHERE a unei declarații SELECT, poate fi scrisă în declarația DELETE, inclusiv subinterogări în clauza WHERE.

Să dăm un exemplu.

Să presupunem că aveți nevoie să eliminați toate persoanele de vânzări care locuiesc în SUA din tabelul esqlSalesPerson. Deși tabelul nostru are City, nu are și country. Putem ocoli acest lucru folosind o subîntrebare.

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

Rețineți, am înfășurat exemplul într-o tranzacție pentru a nu șterge permanent datele mele de test.

Subîntrebarea este colorată în albastru. Observați că nu este diferită de orice altă subîntrebare corelată pe care este posibil să o fi folosit în trecut.

Mecanica acestei instrucțiuni DELETE este:

1. Căutați orașe distincte pentru toate persoanele care locuiesc în SUA.
2. DELETE persoanele din vânzări al căror oraș se află în această listă.

Primul pas rezultă din subîntrebare. Al doilea pas șterge rândurile conform clauzei WHERE.

.