Pebellslearn by building
0%
RoadmapsMatrixCoursesSandboxSign in
Data Manipulation Language (DML)  ›  Capstone Project

DELETE — Removing Rows

DELETE 15 minDELETE removes whole rows chosen by WHERE, is blocked by foreign keys that still reference those rows, and is often replaced by a soft delete in real systems.

DELETE — Removing Rows

A customer asks for her test orders from last week to be removed. It takes one line of SQL — and a slightly wrong line can remove far more than she asked for. DELETE follows the same pattern as UPDATE, and it deserves the same care.

The rule

DELETE removes whole rows; WHERE decides which — preview it first.

DELETE FROM orders
WHERE status = 'cancelled' AND order_date < '2026-01-01';

DELETE always removes entire rows, and every row that matches the WHERE goes, whether that is one row or a million. There is no column list: you cannot delete "just the email" of a customer. To blank out one value, use UPDATE and set it to NULL instead.

As with UPDATE, leaving out WHERE removes every row in the table. The table itself stays, empty — unlike DROP.

Foreign keys protect related data. If orders references customers, then

DELETE FROM customers WHERE customer_id = 1;

is refused while Asha still has orders, because those orders would point at nobody. You either delete her orders first, or the foreign key was declared with ON DELETE CASCADE, in which case her orders are removed automatically along with her. You will see those options in the constraints section.

Many real systems avoid deleting at all. They add a column such as deleted_at and set it with UPDATE, a pattern called a soft delete. The row stays for history and audits, and queries simply ignore it.

The shortcut

Same trick as UPDATE: select first, then swap. Run SELECT * with your WHERE, count the rows, then replace SELECT * with DELETE, keeping everything after FROM unchanged.

Removing Kabir's test ordersStep 1 of 4

  1. Write the SELECT

    SELECT * FROM orders WHERE customer_id = 4 AND status = 'test';

The trap: typing the DELETE line, getting distracted, and running it before the WHERE line is on the screen. Writing the SELECT first means the WHERE exists before the word DELETE does.

Drills

Answer fast — trust the rule.

quick drill

What does DELETE FROM orders; do?

quick drill

You want to remove Ravi's email address but keep his customer record. Which statement?

quick drill

DELETE FROM customers WHERE customer_id = 1; fails. Asha has three orders, and orders.customer_id is a foreign key to customers. Why?

quick drill

What is a "soft delete"?

Remember

  • DELETE removes whole rows; WHERE decides which — preview it first.
  • Select first, then swap SELECT * for DELETE.
  • One value goes with UPDATE … NULL; foreign keys block deleting rows that others still point to.

Practice — level up

0/1 solved

Write your answer, then compare it with a rubric and a model answer.

ExplainRemove a customer properly

A customer, Kabir (customer_id 4), asks the shop to remove him. He has two orders. Explain what happens if you run DELETE FROM customers WHERE customer_id = 4; with a foreign key from orders, and describe two different ways the shop could handle his request, with one advantage of each.