Removing data from a database sounds simple, but a single missing clause can wipe out an entire table. In this tutorial, you will learn how to use the SQL DELETE statement safely: how to target the exact rows you want to remove, how to verify your target before deleting, and how to use transactions to recover from mistakes.
By the end, you will be able to confidently delete rows without risking accidental data loss.
Prerequisites
To follow along, you should:
- Have a working SQL database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, or SQLite).
- Know how to run SQL statements in a client such as
psql, MySQL Workbench, DBeaver, or the command line. - Be familiar with
SELECTandWHEREfrom earlier tutorials.
If you are new to SQL, start with How do I retrieve data using a SELECT statement? and How do I filter SQL query results using WHERE?
Sample Database
We will use an online bookstore as our sample domain. Create a customers table and insert a few rows so you can practice deletions safely.
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL,
status VARCHAR(20) NOT NULL,
created_at DATE NOT NULL
);
INSERT INTO customers (customer_id, customer_name, email, status, created_at) VALUES
(1, 'Alice Johnson', '[email protected]', 'active', '2024-01-15'),
(2, 'Bob Smith', '[email protected]', 'inactive', '2023-06-20'),
(3, 'Carol Davis', '[email protected]', 'active', '2024-03-10'),
(4, 'David Miller', '[email protected]', 'inactive', '2022-11-05'),
(5, 'Eva Thompson', '[email protected]', 'active', '2025-02-28');
This gives us five customers with a mix of active and inactive statuses.
Warning: Run every example in this tutorial in a disposable learning database, never in production.
Basic Syntax
The general form of a DELETE statement is:
DELETE FROM table_name
WHERE condition;
Key points:
DELETE FROM table_namenames the table you want to remove rows from.WHERE conditiondecides which rows are removed.- Without a
WHEREclause, every row in the table is deleted.
DELETE removes rows but keeps the table structure (columns, indexes, constraints) intact.
Step 1: Verify the Target Rows First
Before running any DELETE, always run a SELECT with the same WHERE clause. This is the single most important habit to prevent data loss.
Suppose we want to remove the customer with customer_id = 4. First, check exactly which rows match:
SELECT customer_id, customer_name, email, status
FROM customers
WHERE customer_id = 4;
Expected Result
| customer_id | customer_name | status | |
|---|---|---|---|
| 4 | David Miller | [email protected] | inactive |
The result shows exactly one row, which is what we expect. Now the DELETE is safe to run.
Step 2: Perform the Delete
Reuse the same WHERE clause you just verified:
DELETE FROM customers
WHERE customer_id = 4;
The database will report the number of rows affected (for example, 1 row deleted). Confirm the row is gone:
SELECT customer_id, customer_name
FROM customers
ORDER BY customer_id;
Expected Result
| customer_id | customer_name |
|---|---|
| 1 | Alice Johnson |
| 2 | Bob Smith |
| 3 | Carol Davis |
| 5 | Eva Thompson |
Additional Examples
Deleting Multiple Rows with a Condition
Remove all customers whose status is inactive:
SELECT customer_id, customer_name, status
FROM customers
WHERE status = 'inactive';
If the result matches the rows you intend to delete, run:
DELETE FROM customers
WHERE status = 'inactive';
Deleting Rows That Match a Date Condition
Remove customers created before 2024:
DELETE FROM customers
WHERE created_at < DATE '2024-01-01';
Handling NULL in Delete Conditions
If a column allows NULL, remember that NULL is not a value you can compare with =. To delete rows where email is missing:
DELETE FROM customers
WHERE email IS NULL;
Never write WHERE email = NULL. That condition is never true, so no rows are deleted, and you might incorrectly assume the table has no such rows.
Using a Transaction to Stay Safe
A transaction lets you delete rows and then decide whether to keep the change (COMMIT) or undo it (ROLLBACK).
BEGIN;
DELETE FROM customers
WHERE status = 'inactive';
-- Inspect the effect before committing
SELECT customer_id, customer_name, status
FROM customers;
-- If something looks wrong:
ROLLBACK;
-- If everything looks correct:
-- COMMIT;
Explanation:
BEGINstarts a transaction.- The
DELETEremoves matching rows, but the change is not yet permanent. - You verify the result with a
SELECT. ROLLBACKreverts the deletion.COMMITmakes it permanent.
Transaction behavior varies between database systems and storage engines. In MySQL, for example, the underlying engine must be transactional (InnoDB) for ROLLBACK to work.
Using Parameters from Application Code
When you delete from Java, always use parameterized queries instead of string concatenation:
String sql = """
DELETE FROM customers
WHERE customer_id = ?
""";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setLong(1, customerId);
int rowsDeleted = statement.executeUpdate();
System.out.println(rowsDeleted + " row(s) deleted.");
}
Parameters protect against SQL injection and correctly handle data types.
Common Mistakes
Forgetting the WHERE Clause
Incorrect:
DELETE FROM customers;
This removes every row in the customers table. Unless you truly intend that, always include a WHERE clause.
Comparing to NULL with =
Incorrect:
DELETE FROM customers
WHERE email = NULL;
Correct:
DELETE FROM customers
WHERE email IS NULL;
NULL represents missing information, so equality comparisons with NULL always return unknown, not true.
Confusing DELETE with TRUNCATE and DROP
DELETEremoves rows and can be rolled back inside a transaction.TRUNCATEremoves all rows quickly but is usually non-transactional and may reset auto-increment counters.DROP TABLEremoves the entire table, including its structure.
Do not use TRUNCATE or DROP TABLE when you only want to remove selected rows.
Ignoring Foreign Key Constraints
If another table references the row you are deleting (for example, an orders table with a customer_id foreign key), the database may:
- reject the delete;
- cascade the delete to related rows (
ON DELETE CASCADE); - set related columns to
NULL(ON DELETE SET NULL).
Understand the constraints before deleting parent rows.
Database Compatibility
The basic DELETE ... WHERE ... syntax is standard SQL and works in:
- PostgreSQL
- MySQL
- MariaDB
- SQL Server
- Oracle Database
- SQLite
Differences to be aware of:
DELETEwithJOIN: MySQL, MariaDB, and SQL Server support multi-table delete syntax. PostgreSQL usesDELETE ... USING. Oracle and SQLite require subqueries.RETURNINGclause: PostgreSQL and Oracle (RETURNING INTO) support returning deleted rows. MySQL and SQLite do not.- Auto-commit: Some clients auto-commit each statement. Explicitly use
BEGIN/COMMITwhen you need transactional safety.
When in doubt, consult the documentation for your database version.
Best Practices
- Always run
SELECTfirst with the sameWHEREclause you plan to use inDELETE. - Never omit
WHEREunless you deliberately want to empty the table. - Wrap risky deletes in a transaction so you can
ROLLBACKon mistakes. - Back up important data before large or irreversible deletions.
- Use parameterized queries in application code to avoid SQL injection.
- Understand foreign keys and cascading rules before deleting parent rows.
- Prefer a soft delete (for example,
status = 'inactive'or adeleted_attimestamp) when you may need to recover the data later. - Use least-privilege accounts: application users should only have
DELETErights on tables where it is necessary.
Conclusion
You learned how to delete data safely from a SQL table by verifying the target rows with SELECT, applying DELETE with a precise WHERE clause, and using transactions to guard against mistakes. The most important rule is simple: never run a DELETE you have not first previewed with SELECT.
