Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 39 of 49

SQL DELETE and TRUNCATE: removing rows

Remove rows with SQL DELETE and WHERE, see how foreign keys block deletes, and how TRUNCATE and DROP TABLE differ, checked with SELECT.

12 minChanging dataUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonDelete chosen rows safely with DELETE ... WHERE, understand why a foreign key can refuse a delete, and explain the difference between DELETE, TRUNCATE and DROP TABLE.

What you will be able to do

After this lesson you can remove rows with confidence: you preview them, delete them, check the result, and understand why the database sometimes refuses.

The basic DELETE

text
DELETE FROM table_name
WHERE condition;

There are no column names, because a delete removes whole rows. As with UPDATE, run a SELECT with the same WHERE first. Employee 20, Aman, was assigned to project 4 for 120 hours, and that assignment was a mistake:

sql
SELECT employee_id, project_id, hours FROM assignments WHERE project_id = 4 ORDER BY employee_id;
result
+-------------+------------+-------+
| employee_id | project_id | hours |
+-------------+------------+-------+
|           6 |          4 |    60 |
|           7 |          4 |   340 |
|           8 |          4 |   310 |
|           9 |          4 |   220 |
|          20 |          4 |   120 |
+-------------+------------+-------+
sql
DELETE FROM assignments
WHERE employee_id = 20 AND project_id = 4;

SELECT employee_id, project_id, hours FROM assignments WHERE project_id = 4 ORDER BY employee_id;
result
+-------------+------------+-------+
| employee_id | project_id | hours |
+-------------+------------+-------+
|           6 |          4 |    60 |
|           7 |          4 |   340 |
|           8 |          4 |   310 |
|           9 |          4 |   220 |
+-------------+------------+-------+

Both columns are in the condition because the primary key of assignments is the pair (employee, project). Using only employee_id = 20 would remove all of Aman's assignments.

Deleting many rows

The condition can match any number of rows. Remove assignments with fewer than 100 hours, counting them before and after:

sql
SELECT COUNT(*) AS short_assignments FROM assignments WHERE hours < 100;
result
+-------------------+
| short_assignments |
+-------------------+
|                 4 |
+-------------------+
sql
DELETE FROM assignments
WHERE hours < 100;

SELECT COUNT(*) AS short_assignments_left FROM assignments WHERE hours < 100;
SELECT COUNT(*) AS assignments_total FROM assignments;
result
+------------------------+
| short_assignments_left |
+------------------------+
|                      0 |
+------------------------+
+-------------------+
| assignments_total |
+-------------------+
|                14 |
+-------------------+

The preview said how many rows to expect, and the total confirms it: 19 originally, minus Aman's, minus the short ones.

Deleting based on another table

A subquery can pick the rows. Delete the assignments of projects that finished before the end of 2024:

sql
SELECT a.project_id, p.name, p.end_date, COUNT(*) AS assignments
FROM assignments a
JOIN projects p ON p.id = a.project_id
GROUP BY a.project_id, p.name, p.end_date
ORDER BY a.project_id;
result
+------------+-------------------+------------+-------------+
| project_id |       name        |  end_date  | assignments |
+------------+-------------------+------------+-------------+
|          1 | Customer Portal   | 2024-09-30 |           4 |
|          2 | Mobile App        | NULL       |           4 |
|          3 | Spring Campaign   | 2025-04-30 |           2 |
|          4 | Q3 Sales Push     | 2024-09-30 |           3 |
|          5 | Billing Migration | NULL       |           1 |
+------------+-------------------+------------+-------------+
sql
DELETE FROM assignments
WHERE project_id IN (SELECT id FROM projects WHERE end_date < '2025-01-01');

SELECT project_id, COUNT(*) AS assignments FROM assignments GROUP BY project_id ORDER BY project_id;
result
+------------+-------------+
| project_id | assignments |
+------------+-------------+
|          2 |           4 |
|          3 |           2 |
|          5 |           1 |
+------------+-------------+

Projects 1 and 4 ended in September 2024, so their assignments are gone. Projects with a NULL end date are not matched, because NULL < '2025-01-01' is not true.

When a foreign key refuses

employees.department_id points at departments.id. Support (department 5) still has employees, so the database will not let you delete it:

sql
SELECT id, name FROM departments WHERE id = 5;
DELETE FROM departments WHERE id = 5;
result
+----+---------+
| id |  name   |
+----+---------+
|  5 | Support |
+----+---------+
Error near line 2: FOREIGN KEY constraint failed

The error message is SQLite's wording; other databases say something different but the effect is the same. The department is still there:

sql
SELECT id, name FROM departments WHERE id = 5;
result
+----+---------+
| id |  name   |
+----+---------+
|  5 | Support |
+----+---------+

The rule is: delete from the child table first, or change the references, then delete the parent. A department with no employees and no projects, such as Research (6), has nothing pointing at it, so it can go:

sql
SELECT id, name FROM departments ORDER BY id;
result
+----+-------------+
| id |    name     |
+----+-------------+
|  1 | Engineering |
|  2 | Sales       |
|  3 | Marketing   |
|  4 | Finance     |
|  5 | Support     |
|  6 | Research    |
+----+-------------+
sql
DELETE FROM departments WHERE id = 6;

SELECT id, name FROM departments ORDER BY id;
result
+----+-------------+
| id |    name     |
+----+-------------+
|  1 | Engineering |
|  2 | Sales       |
|  3 | Marketing   |
|  4 | Finance     |
|  5 | Support     |
+----+-------------+

Some databases can be told to cascade, deleting the children automatically with ON DELETE CASCADE. That is powerful and dangerous, and it belongs to the lesson on keys.

Emptying a table: DELETE, TRUNCATE and DROP

Statement What it does
DELETE FROM t Removes all rows, one by one, and can be rolled back inside a transaction. The table stays.
TRUNCATE TABLE t Empties the table in one fast operation. The table stays. Not part of SQLite.
DROP TABLE t Removes the table and its data completely.

SQLite has no TRUNCATE, so this block is shown but not run:

sql
TRUNCATE TABLE assignments_copy;

In SQLite, DELETE FROM with no WHERE does the same job. Here it is on a throwaway copy of a table:

sql
CREATE TABLE assignments_copy AS SELECT * FROM assignments;

SELECT COUNT(*) AS rows_before FROM assignments_copy;

DELETE FROM assignments_copy;

SELECT COUNT(*) AS rows_after FROM assignments_copy;
result
+-------------+
| rows_before |
+-------------+
|           7 |
+-------------+
+------------+
| rows_after |
+------------+
|          0 |
+------------+

The table still exists with zero rows. The exact behaviour of TRUNCATE varies: in MySQL it cannot be rolled back and resets auto-increment counters, in PostgreSQL it can be rolled back inside a transaction, and SQL Server also allows it in a transaction but needs more privileges and refuses it on tables that a foreign key references. Check your database's documentation before relying on any of that.

Soft deletes

Many real systems rarely run DELETE on important data. Instead they add a column such as deleted_at or is_active and UPDATE it, so the row stays in the table and queries filter it out. This keeps history, allows undoing a mistake, and avoids foreign key trouble. The trade-off is that every query must remember the filter. Whether you delete for real or mark rows as deleted depends on your rules for retention, privacy and auditing, so decide deliberately rather than by habit.

Differences between databases

  • TRUNCATE is available in MySQL, PostgreSQL and SQL Server, but not in SQLite.
  • CREATE TABLE ... AS SELECT works in MySQL, PostgreSQL and SQLite; SQL Server uses SELECT ... INTO new_table FROM ....
  • Deleting with a join is written differently in each database. A subquery in WHERE, as above, works everywhere. See the MySQL DELETE lesson for MySQL specifics.

Common mistakes

  • Forgetting WHERE. The whole table is emptied. Preview with a SELECT and consider a transaction.
  • Deleting by a non-unique column. Check the row count before and after.
  • Deleting the parent first. The foreign key will refuse, or with cascade you may delete far more than you meant.
  • Confusing DELETE with DROP. DELETE removes rows, DROP TABLE removes the table.
  • Assuming you can undo it. Without a transaction or backup, a delete is final.

Exercise

Remove employee 12 (Yash) completely. He has an assignment on project 3, so that has to go first. Show his assignments and his employee row before, and confirm both are gone afterwards.

Show one possible answer
sql
SELECT employee_id, project_id, hours FROM assignments WHERE employee_id = 12;
SELECT id, first_name FROM employees WHERE id = 12;
result
+-------------+------------+-------+
| employee_id | project_id | hours |
+-------------+------------+-------+
|          12 |          3 |   190 |
+-------------+------------+-------+
+----+------------+
| id | first_name |
+----+------------+
| 12 | Yash       |
+----+------------+
sql
DELETE FROM assignments WHERE employee_id = 12;
DELETE FROM employees WHERE id = 12;

SELECT COUNT(*) AS assignments_left FROM assignments WHERE employee_id = 12;
SELECT COUNT(*) AS employees_left FROM employees WHERE id = 12;
result
+------------------+
| assignments_left |
+------------------+
|                0 |
+------------------+
+----------------+
| employees_left |
+----------------+
|              0 |
+----------------+

Key points

  • DELETE FROM table WHERE condition removes the matching rows; without WHERE it removes every row but keeps the empty table.
  • Preview a delete by running a SELECT with the same WHERE first, and compare the row count after.
  • A foreign key stops you deleting a parent row that child rows still point to; delete the children first or change the references.
  • TRUNCATE TABLE empties a whole table quickly and is not available in SQLite; DROP TABLE removes the table itself.
  • Deleting is permanent unless you are inside a transaction or have a backup.

Check yourself

1. What does DELETE FROM employees do with no WHERE?
2. You try to delete department 5, but employees still belong to it and a foreign key exists. What happens?
3. Which statement removes the table definition as well as its rows?
4. What is a safe way to check a DELETE before running it?