DELETE

DELETE

What it means

DELETE removes rows from a table, optionally restricted by a WHERE clause. Without a WHERE clause, it removes every row in the table (unlike TRUNCATE, it’s logged row-by-row and can trigger DELETE triggers, but is otherwise just as capable of wiping the whole table).

Examples

DELETE FROM Orders WHERE Id = 42;

DELETE FROM Orders WHERE OrderDate < '2020-01-01';

-- Delete based on a join / subquery condition
DELETE FROM Orders
WHERE CustomerId IN (SELECT Id FROM Customers WHERE IsBanned = 1);

-- DANGER: removes every row in the table
DELETE FROM Orders;

Common mistake

Running DELETE FROM TableName; without a WHERE clause, intending to delete only some rows but forgetting the condition — this silently and irreversibly wipes the entire table with no confirmation prompt.

-- Intended to delete one customer's orders, but forgot the WHERE clause entirely
DELETE FROM Orders;   -- deletes ALL orders, for every customer

-- Correct
DELETE FROM Orders WHERE CustomerId = 42;

-- Best practice: run the equivalent SELECT first to verify what would be affected
SELECT * FROM Orders WHERE CustomerId = 42;   -- check this looks right, THEN delete

Quick practice

1. What happens if you run DELETE FROM Orders; with no WHERE clause?

   Answer

   Every row in the Orders table is deleted.

2. What’s a safe habit before running a DELETE with a WHERE clause on production data?

   Answer

   Run the equivalent SELECT with the same WHERE clause first, to verify exactly which rows would be affected before actually deleting them.

3. Can a DELETE statement’s WHERE clause reference a subquery?

   Answer

   Yes — e.g. DELETE FROM Orders WHERE CustomerId IN (SELECT Id FROM Customers WHERE IsBanned = 1);