Delete - DELETE
DELETE removes rows from a table. Same rule as UPDATE: always pair it with WHERE, or it removes every row.
Basic syntax
DELETE FROM table_name
WHERE condition;Deleting one row
DELETE FROM books
WHERE title = 'Brave New World';Deleting multiple rows
Any row matching the condition gets removed:
DELETE FROM books
WHERE in_stock = FALSE;The most important rule
Leaving off WHERE deletes every row in the table:
-- Danger: this deletes ALL books
DELETE FROM books;The table itself still exists afterward — just empty. Compare that to DROP TABLE books;, which removes the table's structure entirely.
Preview before you delete
Run the same condition as a SELECT first, so you know exactly what's about to disappear:
SELECT * FROM books WHERE in_stock = FALSE;If that returns the rows you expect, swap it for DELETE.
DELETE vs TRUNCATE vs DROP
| Command | What it does | Keeps table structure? |
|---|---|---|
DELETE | Removes specific rows matching a condition | Yes |
TRUNCATE | Removes all rows, resets auto-increment | Yes |
DROP | Deletes the table entirely, structure included | No |
TRUNCATE is faster than a DELETE with no WHERE, but it can't be filtered — it always removes everything:
TRUNCATE TABLE books;MySQL's safety net
Just like UPDATE, MySQL's "safe update mode" can block a DELETE that doesn't reference a key column in WHERE (Error 1175). That's intentional protection against accidental mass deletes.
Quick Reference
| Goal | Example |
|---|---|
| Delete one row | DELETE FROM books WHERE title = 'Brave New World'; |
| Delete multiple rows | WHERE in_stock = FALSE |
| Preview before deleting | Run the same WHERE as a SELECT first |
| Wipe all rows, keep table | TRUNCATE TABLE books; |
| Remove table entirely | DROP TABLE books; |
-- Deleting one row
DELETE FROM books
WHERE title = 'Brave New World';
-- Deleting multiple rows
DELETE FROM books
WHERE price > 15;
-- Deleting all rows (be careful!)
DELETE FROM books;