The SQL DELETE Statement
The DELETE statement removes rows from a table based on a condition:
DELETE FROM table_name
WHERE condition;We will use this Employees table:
+----+-----------+------------+--------+-----------+
| ID | Name | Department | Salary | City |
+----+-----------+------------+--------+-----------+
| 1 | Priya K. | Sales | 55000 | Mumbai |
| 2 | John D. | Marketing | 62000 | London |
| 3 | Arun M. | IT | 78000 | Delhi |
| 4 | Emma W. | Sales | 51000 | Sydney |
| 5 | Ravi S. | IT | 72000 | Mumbai |
| 6 | Sara L. | HR | 58000 | New York |
+----+-----------+------------+--------+-----------+Delete a Specific Row
Use a WHERE clause that uniquely identifies the row, typically by primary key:
DELETE FROM Employees
WHERE ID = 4;+----+-----------+------------+--------+-----------+ | ID | Name | Department | Salary | City | +----+-----------+------------+--------+-----------+ | 1 | Priya K. | Sales | 55000 | Mumbai | | 2 | John D. | Marketing | 62000 | London | | 3 | Arun M. | IT | 78000 | Delhi | | 5 | Ravi S. | IT | 72000 | Mumbai | | 6 | Sara L. | HR | 58000 | New York | +----+-----------+------------+--------+-----------+ 5 rows remaining (Emma W. deleted)
Delete Multiple Rows
When the WHERE clause matches multiple rows, all matching rows are deleted. You can use AND, OR operators to combine conditions:
DELETE FROM Employees
WHERE Department = 'Sales';Priya K. and Emma W. are removed. 4 rows remaining.
Delete employees earning below 55,000:
DELETE FROM Employees
WHERE Salary < 55000;DELETE Without WHERE — Deletes Everything
⚠️ Critical Warning
Running DELETE without a WHERE clause removes every row from the table. The table structure remains, but all data is gone. This is almost always a catastrophic mistake.
-- ⚠️ This deletes ALL employees!
DELETE FROM Employees;After this, the Employees table exists but contains zero rows. All data is permanently lost unless you have a backup.
DELETE vs TRUNCATE vs DROP
| Command | What It Does | WHERE Clause? | Can Undo? |
|---|---|---|---|
DELETE |
Removes specific rows | Yes | Yes (with transaction) |
TRUNCATE |
Removes ALL rows (faster) | No | No in most databases |
DROP TABLE |
Removes the entire table | No | No |
Best Practices for DELETE
| Practice | Why |
|---|---|
| Always use WHERE | Prevents deleting all rows accidentally |
| Test with SELECT first | Preview which rows will be deleted |
| Use primary key | Guarantees you delete exactly one row |
| Back up before bulk deletes | Deleted data cannot be recovered without backup |
💡 Pro Tip: Test Before You Delete
Replace DELETE FROM with SELECT * FROM using the same WHERE clause. Verify the correct rows are shown, then switch back to DELETE.
-- Step 1: Preview
SELECT * FROM Employees
WHERE City = 'Sydney';
-- Step 2: Delete if correct
DELETE FROM Employees
WHERE City = 'Sydney';For a complete reference of the DELETE syntax, see the official MySQL DELETE documentation. In the next chapter, you will learn how to limit the number of rows returned with SQL TOP, LIMIT, and FETCH FIRST.
If you work with data regularly, explore our ready-made Dashboard Templates on NextGenTemplates that use SQL-powered data for business reporting.
📺 Visit our YouTube channel @NeoTechNavigators for step-by-step video tutorials and dashboard demos on this topic.
🧪 Try It Yourself
Write a DELETE statement that removes all employees from Mumbai. Then write a SELECT to verify they are gone.
DELETE FROM Employees
WHERE City = 'Mumbai';
-- Verify
SELECT * FROM Employees;Expected Output (after delete):
+----+-----------+------------+--------+-----------+ | ID | Name | Department | Salary | City | +----+-----------+------------+--------+-----------+ | 2 | John D. | Marketing | 62000 | London | | 3 | Arun M. | IT | 78000 | Delhi | | 4 | Emma W. | Sales | 51000 | Sydney | | 6 | Sara L. | HR | 58000 | New York | +----+-----------+------------+--------+-----------+
📝 What You Learned in This Chapter
DELETE FROMremoves rows from a table- Always use a WHERE clause to target specific rows
- DELETE without WHERE removes ALL rows from the table
- Test with SELECT first to preview which rows will be deleted
- DELETE removes rows but keeps the table structure intact
- TRUNCATE removes all rows faster; DROP removes the entire table
Frequently Asked Questions
DELETE removes specific rows using a WHERE clause and can be rolled back inside a transaction. TRUNCATE removes all rows at once, is faster on large tables, resets auto-increment counters, and cannot be rolled back in most databases.ROLLBACK to undo. If auto-commit is on (the default), the delete is permanent immediately. Always test with SELECT first and back up important data.DROP TABLE.📖 Chapter 11 of 30 — SQL Tutorial on Neotech Navigators
