Basic Queries  «Prev  Next»

Lesson 7 Using DELETE
Objective Delete a row from a table.

Remove Rows Using the DELETE Statement

Over time, a table can accumulate rows nobody needs anymore: a book discontinued for poor sales, a customer who never completes a purchase. Periodically removing that kind of row keeps a table smaller and faster to search, and frees up disk space that might otherwise be needed for other purposes, such as the swap space an operating system uses to temporarily move information out of main memory when it runs low.

Deleting data deserves real caution, though, since it's considerably harder to undo than most other mistakes in SQL. It's good practice to archive a row's data somewhere before removing it from a production database, a live database an actual application depends on, rather than simply hoping it's never needed again. This lesson's closing sections show exactly how to do that in the same statement that performs the deletion.

The DELETE statement removes rows from a table. Its syntax looks a lot like SELECT's, since both name a table and can include a WHERE clause, but DELETE never names individual columns, since an entire row is removed at once, not a value within it. The keyword FROM is technically optional in T-SQL; DELETE FROM Inventory and DELETE Inventory do exactly the same thing. This lesson writes FROM explicitly throughout, since it reads more clearly and matches the SELECT statements everywhere else in this module.

Leaving off the WHERE clause entirely is exactly as dangerous here as it is with UPDATE. The following statement removes every single row from VirtualBookShelf's entire catalog, with no way to select which ones:
DELETE FROM Inventory;
That's rarely what anyone actually wants. For a safer, more realistic example, suppose "Midnight Harbor," one of the three books added to Inventory back in Lesson 6's multi-row INSERT example, turns out to sell poorly and VirtualBookShelf.com decides to stop carrying it. The following figures show how a properly targeted DELETE statement removes only that one row.

Inventory, before the deletion
BookID Title Author
1The Last LighthouseBennett
2Coastal KitchensAlvarez
3Deep WinterCastellano
4Algorithms SimplifiedChen
5Building With StoneWhitfield
6Harbor LightsBennett
7Midnight HarborAlvarez
8Garden AlmanacWhitfield
9Pocket AstronomyChen
1) Nine books are currently in Inventory, including "Midnight Harbor," BookID 7.

DELETE FROM Inventory
WHERE Title = 'Midnight Harbor';
2) This statement removes every row where the title is "Midnight Harbor." In this table, that's exactly one row. The same WHERE clause rules apply as always; comparison, logical, and pattern-matching operators all work here exactly as they do in SELECT and UPDATE.

Inventory, after the deletion
BookID Title Author
1The Last LighthouseBennett
2Coastal KitchensAlvarez
3Deep WinterCastellano
4Algorithms SimplifiedChen
5Building With StoneWhitfield
6Harbor LightsBennett
8Garden AlmanacWhitfield
9Pocket AstronomyChen
3) Eight books remain. Notice BookID 7 is simply gone, not reused; the next book inserted will still get BookID 10, continuing from where the IDENTITY column left off, not filling the gap left behind.

Writing and Running a DELETE Statement Yourself

Running a DELETE statement in Azure Data Studio follows the exact same mechanics as every other statement in this module: connect to the SQL Server 2025 instance, open a query window against the VirtualBookShelf database, and type the statement before pressing Execute. The one habit worth building specifically for DELETE, more than for any other statement, is running the WHERE clause as a plain SELECT first, covered in its own section further down this lesson, since a mistaken UPDATE can usually be corrected with a second UPDATE, but a mistaken DELETE has nothing left to correct once it commits.

After execution, the Messages pane reports how many rows were removed, for example "(1 row affected)" for figure 2's statement above. A statement reporting far more rows affected than expected is worth stopping to look at immediately, ideally by rolling the transaction back if it hasn't been committed yet, rather than assuming the count is simply wrong.

Archiving a Row Before It's Gone

The caution at the top of this lesson, archive before you delete, has a direct SQL answer: the OUTPUT clause, already introduced for UPDATE in Lesson 5 and INSERT in Lesson 6, also works with DELETE. Here, it only ever uses the deleted pseudo-table, the mirror image of INSERT's inserted-only case, since a row that's being removed has a "before" state but no "after" one.

Adding an INTO clause after OUTPUT sends those captured values straight into another table, in the same atomic statement that performs the deletion, rather than just displaying them and hoping someone copies them down first.
DELETE FROM Inventory
OUTPUT deleted.BookID, deleted.Title, deleted.Author, deleted.Price, SYSUTCDATETIME() AS DeletedAtUtc
INTO InventoryArchive (BookID, Title, Author, Price, DeletedAtUtc)
WHERE Title = 'Midnight Harbor';
This removes the row from Inventory and writes a permanent copy of it, along with the exact moment it was removed, into a separate InventoryArchive table, all in one statement. There's no window where the row exists in neither place, and no window where it briefly exists in both; the deletion and the archival happen together, or neither happens at all. SYSUTCDATETIME() simply returns the current date and time, in UTC, at the moment the statement runs.

Deleting Rows That Don't Match a Pattern

Lesson 3 introduced REGEXP_LIKE for filtering rows in a WHERE clause, and Lesson 5 introduced REGEXP_REPLACE for fixing a malformed value in place rather than deleting the row it belongs to. Sometimes a row is malformed badly enough that fixing it doesn't make sense, and removing it outright is the more honest option. REGEXP_LIKE works just as well inside a DELETE statement's WHERE clause as it does in a SELECT.
DELETE FROM Customer
WHERE NOT REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
This removes every Customer row whose Email doesn't even loosely resemble a real email address, the NOT in front of REGEXP_LIKE flipping the match so the statement targets rows that fail the pattern rather than ones that pass it. A statement like this is exactly the kind of thing worth archiving first with the technique from the previous section, since a pattern that's slightly too strict could end up matching legitimate rows it shouldn't.

It's worth running the pattern as a SELECT first here too, the same habit covered a little further down this lesson: SELECT CustomerID, Email FROM Customer WHERE NOT REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'); shows exactly which rows the matching DELETE would remove, letting a human glance over the list before anything is actually gone. A regular expression that's even slightly off can match far more, or far less, than intended, and that's a mistake worth catching before it becomes permanent rather than after.

DELETE Versus TRUNCATE TABLE

This lesson opened with a statement that deletes an entire table's contents, DELETE FROM Inventory with no WHERE clause at all. SQL Server has a second, different statement for that exact situation, TRUNCATE TABLE, and the two aren't interchangeable.
TRUNCATE TABLE Inventory;
TRUNCATE TABLE removes every row in a table, the same end result as an unfiltered DELETE, but it does so by deallocating entire data pages at once rather than removing rows one at a time, which makes it considerably faster and lighter on the transaction log for a genuinely large table. It comes with real restrictions, though: it can never take a WHERE clause, so it's all rows or none; it resets an IDENTITY column's counter back to its starting value, so the next row inserted afterward would get BookID 1 again rather than continuing from 10; and it's blocked outright if any other table has a foreign key pointing at the table being truncated. DELETE has none of these restrictions, which is exactly why it's the statement this lesson has used throughout, even though TRUNCATE TABLE would technically be faster for the all-rows case shown at the very top.

The Same Delete, Three Ways

This module has compared SELECT, WHERE, ORDER BY, UPDATE, and INSERT across raw SQL, ADO.NET, and Entity Framework Core. DELETE fits the same comparison, and like every other statement that changes data rather than only reading it, it should never be built by concatenating user input directly into the SQL text.
-- Raw SQL, run directly in Azure Data Studio
DELETE FROM Inventory
WHERE Title = 'Midnight Harbor';
// ADO.NET: parameters, never string concatenation
using var cmd = new SqlCommand(
    "DELETE FROM Inventory WHERE Title = @title;", connection);
cmd.Parameters.AddWithValue("@title", "Midnight Harbor");
int rowsAffected = cmd.ExecuteNonQuery();
// Entity Framework Core: find the entity, then remove and save
var book = context.Inventory.Single(b => b.Title == "Midnight Harbor");
context.Inventory.Remove(book);
context.SaveChanges();
EF Core's version doesn't call anything resembling DELETE either. It loads the tracked entity, marks it for removal with Remove(), and generates the actual DELETE statement only once SaveChanges() runs, targeting that one row by its primary key underneath. cmd.ExecuteNonQuery() returns the same integer row count here as it does for UPDATE and INSERT, useful for confirming a DELETE removed exactly one row rather than zero or several.

Checking Before You Delete

Before running an unfamiliar DELETE statement, it's worth running its WHERE clause as a plain SELECT first, to confirm exactly which rows it would reach.
SELECT BookID, Title, Author
FROM Inventory
WHERE Title = 'Midnight Harbor';
If this returns exactly the row expected, and no others, the equivalent DELETE statement is safe to run with confidence. If it returns nothing at all, the DELETE would simply report zero rows affected, harmless but probably a sign of a typo somewhere in the condition. If it returns more rows than expected, that's exactly the moment to stop and narrow the WHERE clause further, before anything is actually removed.

For anything beyond a quick, obviously safe deletion, Azure Data Studio also supports wrapping the whole thing in an explicit transaction, running BEGIN TRANSACTION, then the DELETE, then checking the result, and only running COMMIT TRANSACTION once satisfied it did the right thing. ROLLBACK TRANSACTION instead undoes everything since BEGIN TRANSACTION, as though the DELETE had never run at all. This isn't a replacement for checking first with a SELECT, it's a second safety net underneath it, useful specifically because it keeps the option to back out open for as long as the transaction stays uncommitted.

A Few Common Questions

Can a DELETE statement be undone after it's already been committed? Not by SQL Server itself. Once a transaction commits, the removed rows are gone from the table for good as far as ordinary SQL is concerned; recovering them means restoring a database backup taken before the deletion, or pulling them back from an archive table set up in advance, exactly the kind of safety net the OUTPUT ... INTO section above builds. This is the real reason that section exists: not because DELETE is unusually dangerous syntax, but because it's one of the few statements in SQL with no built-in undo once it's done.

Can a DELETE statement remove rows based on a value in a second, related table? Yes, using either DELETE ... FROM ... JOIN or an EXISTS subquery, but both depend on JOIN syntax a later lesson in this course will cover. Every example in this lesson filters Inventory or Customer using only values already present in the row being deleted.

What happens when a row can't be deleted because another table depends on it? SQL Server raises an error and cancels the DELETE entirely, rather than leaving the database in a broken, half-consistent state. This happens when a foreign key constraint on another table points at the row being deleted, without that constraint being configured to cascade the deletion automatically. VirtualBookShelf's schema, as this course has built it so far, doesn't yet have a table that references Inventory or Customer this way, so none of this lesson's examples can actually trigger it, but it's a real possibility worth knowing about before a future lesson introduces a table that does.

Is there a safe way to delete an enormous number of rows without one huge, long-running transaction? Yes, by combining DELETE TOP (n) with a loop, deleting a few thousand rows at a time instead of everything in a single statement, checking after each pass how many rows were actually removed and stopping once that number reaches zero. This is a real, useful pattern for a very large table, well beyond the scale this lesson's handful of examples need.

Can a DELETE statement filter on a native json column? Yes, SQL Server 2025 added JSON_CONTAINS() specifically for checking whether a JSON value contains a given element, useful for purging rows tagged a certain way inside a JSON column. VirtualBookShelf's schema doesn't currently include any JSON columns, so this doesn't apply to anything in this lesson, but it's available for an application that stores that kind of data.

Does deleting a lot of rows at once lock the whole table the way older versions of SQL Server might? Less than it used to. SQL Server 2025's Optimized Locking combines two techniques: a single lock on the deleting transaction's own ID, rather than a separate lock for every row, and a technique called Lock After Qualification, which checks each row's last committed value against the WHERE clause before locking it, so rows that don't match never get locked at all. Both techniques depend on accelerated database recovery being enabled on the database; without it, this optimization simply doesn't apply. None of this changes how any statement in this lesson is written, it's an improvement SQL Server 2025 applies automatically underneath.

Can a deleted row's IDENTITY value ever be reused by a later INSERT? Not under ordinary DELETE. Figure 3 already showed this: after "Midnight Harbor" was removed, BookID 7 simply disappeared, and the next row inserted would continue from 10, not reuse 7. TRUNCATE TABLE is the one exception covered in this lesson that does reset the counter entirely, which is one more reason the two statements aren't interchangeable.

Does a DELETE statement fire the same kind of validation as an INSERT or UPDATE, such as a NOT NULL check? Not in the same sense, since a DELETE isn't writing any new values into columns for those checks to apply to. What it does still respect are foreign key constraints, covered above, and any AFTER DELETE trigger defined on the table, a more advanced feature where custom logic runs automatically in response to a deletion. Neither of those exists anywhere in VirtualBookShelf's schema yet, so every example in this lesson runs without any extra validation beyond the WHERE clause itself.

If a DELETE statement's WHERE clause matches zero rows, does that count as an error? No. SQL Server reports "(0 rows affected)" and moves on, exactly the way a WHERE clause matching nothing behaves for UPDATE. It's worth treating a surprising zero the same way the "checking before you delete" section above suggests treating a surprising non-zero count, as a signal to double-check the condition rather than something to simply shrug off.

What's Ahead

This lesson covered removing rows with DELETE: its resemblance to SELECT without a column list, the danger of a missing WHERE clause, archiving a deleted row with OUTPUT ... INTO, removing malformed rows with REGEXP_LIKE, and the real differences between DELETE and TRUNCATE TABLE.

That completes this module's tour of the four statements that read and change data: SELECT to retrieve rows, UPDATE to change them, INSERT to add them, and now DELETE to remove them. The next lesson closes out this module with a quiz covering all four.

SEMrush Software 7 SEMrush Banner 7