DELETE Statement
In this page:
Introduction to DELETE
The DELETE statement removes one or more rows from a table, freeing up that data permanently. It is a permanent action, so use it with caution — there's no built-in undo. Always back up your database before running a delete operation on production data.
Example: Introduction to DELETE
DELETE FROM users WHERE id = 1;The Danger of Missing WHERE Clauses
If you run a DELETE statement without a WHERE clause, all rows in the table will be deleted. The table structure itself will remain, but it will be completely empty, unlike DROP TABLE which removes the structure too. Always triple-check your queries before hitting execute.
Example: The Danger of Missing WHERE Clauses
DELETE FROM users; -- deletes ALL rows, table structure remainsTRUNCATE versus DELETE
TRUNCATE is much faster than DELETE for removing all rows from a table, since it doesn't log each row deletion individually. TRUNCATE resets the table (including AUTO_INCREMENT) and is not logged row-by-row. Use DELETE instead if you need to filter rows with WHERE.
Example: TRUNCATE versus DELETE
TRUNCATE TABLE users; -- faster, resets AUTO_INCREMENT
DELETE FROM users; -- slower, logs each rowDeleting with LIMIT and ORDER BY
You can limit the number of rows deleted by using the LIMIT clause, useful for removing old records gradually. You can also sort the rows first using ORDER BY. This lets you delete specific subsets of older data safely, such as the 100 oldest log entries.
Example: Deleting with LIMIT and ORDER BY
DELETE FROM logs ORDER BY created_at ASC LIMIT 100;Safe Deletion Practices
It is a good practice to test your DELETE query as a SELECT query first, swapping DELETE FROM for SELECT * FROM with the same WHERE. This lets you preview exactly which rows will be removed before committing. Once you are sure, safely convert it into a DELETE statement.
Example: Safe Deletion Practices
SELECT * FROM users WHERE status = 'banned'; -- preview first
DELETE FROM users WHERE status = 'banned';Chapter Quiz — Complete all 4 topics to unlock
0/4 topics done
Complete these topics first: