MySQL DELETE Statement

The DELETE statement removes one or more rows from a table. Unlike DROP TABLE, which removes the entire table structure, DELETE only removes specific data rows while keeping the table and its structure intact.

Syntax

DELETE FROM table_name
WHERE condition;

The WHERE clause specifies which rows to delete. Without it, all rows in the table are deleted.

Sample Table: orders

+----------+------------+--------+-----------+
| order_id | customer   | amount | status    |
+----------+------------+--------+-----------+
|        1 | Alice      | 120.00 | delivered |
|        2 | Bob        |  45.50 | pending   |
|        3 | Carol      | 200.00 | cancelled |
|        4 | David      |  89.00 | delivered |
|        5 | Eve        |  15.00 | cancelled |
+----------+------------+--------+-----------+

Deleting a Single Row

Delete Bob's order (order_id = 2):

DELETE FROM orders
WHERE order_id = 2;

Only the row with order_id = 2 is removed. All other rows remain unchanged.

Deleting Multiple Rows

Delete all orders with the status 'cancelled':

DELETE FROM orders
WHERE status = 'cancelled';

This removes Carol's and Eve's orders in one operation.

Deleting All Rows (Without Dropping the Table)

To remove every row while keeping the table structure:

DELETE FROM orders;

The orders table still exists after this — it is now empty. This is different from DROP TABLE, which removes the table entirely.

DELETE vs TRUNCATE

Using DELETE with ORDER BY and LIMIT

To delete only a specific number of rows matching a condition, combine ORDER BY and LIMIT:

-- Delete only the oldest 2 cancelled orders
DELETE FROM orders
WHERE status = 'cancelled'
ORDER BY order_id ASC
LIMIT 2;

Verifying After Delete

Always verify the result after a delete operation:

SELECT * FROM orders;

Safety Tip

Before running a DELETE, run a SELECT with the same WHERE clause first. This previews which rows will be affected:

-- Step 1: Preview rows to be deleted
SELECT * FROM orders
WHERE status = 'cancelled';

-- Step 2: If the result looks correct, delete those rows
DELETE FROM orders
WHERE status = 'cancelled';

Key Points

• DELETE FROM table WHERE condition; removes specific rows matching the condition.
• Without a WHERE clause, all rows in the table are deleted.
• DELETE keeps the table structure intact — only the data is removed.
• Use a SELECT with the same condition to preview rows before deleting them.
• Inside a transaction, a DELETE can be rolled back — unlike TRUNCATE.