DELETE
Removes rows from a table (use WHERE to avoid deleting everything).
DELETE FROM table_name WHERE condition;This static page keeps the syntax and examples indexed for search, while the coding app handles interactive exploration and saved references.
What it does
Overview
Removes rows from a table (use WHERE to avoid deleting everything).
The DELETE statement is a Data Manipulation Language (DML) command used to remove one or more records from a table based on specific criteria. Unlike the TRUNCATE command—which is a Data Definition Language (DDL) operation that deallocates entire data pages—DELETE identifies and removes individual rows, recording each change in the database transaction log. This logging allows for operations to be rolled back but introduces significant performance overhead when processing large volumes of data. To optimize performance, columns referenced in the WHERE clause should be indexed to avoid expensive full table scans. Developers must also account for referential integrity; if a row is referenced by a foreign key in another table, the deletion may fail unless 'ON DELETE CASCADE' is configured. When managing temporal data, it is best practice to use ISO 8601 strings ('YYYY-MM-DD') for date comparisons to ensure consistent behavior across different database engines and locales.
Quick reference
Syntax
DELETE FROM table_name WHERE condition;Inputs
Parameters
See it in practice
Examples
Basic Conditional Deletion
DELETE FROM users
WHERE user_id = 42;Query OK, 1 row affected
Removes a single record from the 'users' table where the primary key matches the specified ID.
Deleting Records Older Than a Specific Date
DELETE FROM session_logs
WHERE last_active < '2023-01-01T00:00:00Z';Query OK, 1500 rows affected
Removes multiple records based on a date comparison. Using an ISO 8601 string ensures reliable ordering and compatibility.
Deletion with Subquery Filter
DELETE FROM orders
WHERE customer_id IN (
SELECT id FROM customers WHERE status = 'inactive'
);Query OK, 8 rows affected
Deletes rows from the 'orders' table by referencing criteria found in the 'customers' table using a subquery.
Debug faster
Common Errors
LogicError
Cause: Omitting the WHERE clause, which results in the deletion of every row in the table while maintaining the table structure.
Fix: Always perform a SELECT query with the same WHERE clause first to verify which rows will be affected before running the DELETE.
DELETE FROM employees; -- Deletes everyone!ConstraintError
Cause: Violating referential integrity by trying to delete a parent row that still has active child references in another table.
Fix: Either delete the related child records first, or update the Foreign Key constraint to include ON DELETE CASCADE.
DELETE FROM departments WHERE id = 1; -- Fails if employees are still assigned to id 1Runtime support
Compatibility
Source: MDN Web Docs
Common questions
Frequently Asked Questions
Removes rows from a table (use WHERE to avoid deleting everything).
ame: Table to delete from. WHERE condition: Filter for which rows to delete.
LogicError: Always perform a SELECT query with the same WHERE clause first to verify which rows will be affected before running the DELETE. ConstraintError: Either delete the related child records first, or update the Foreign Key constraint to include ON DELETE CASCADE.