How to Delete a Record in SQL

The Basic DELETE Statement

To delete a record in SQL, you use the DELETE statement with a WHERE clause that identifies which row or rows to remove. The basic syntax is DELETE FROM table_name WHERE condition; — without the WHERE clause, SQL will delete every row in the table, which is why the WHERE clause matters.

For example, if you have a table called customers and want to delete the customer with ID 5, you would write: DELETE FROM customers WHERE id = 5; This removes only that one row. If you write DELETE FROM customers; without a WHERE clause, every customer record disappears.

Most database systems (MySQL, PostgreSQL, SQL Server, SQLite) use this same syntax, though some have additional options for safety or performance. Always double-check your WHERE condition before running a DELETE statement, because the deletion is usually permanent and cannot be undone without a backup.

Key Takeaways

The DELETE statement removes rows from a table, and you must include a WHERE clause to specify which rows to delete or you will delete everything.
Test your WHERE condition with a SELECT statement first to confirm you are targeting the correct rows before you run DELETE.
Deletions are permanent in most databases unless you have a backup or transaction rollback available, so verify your condition carefully.
You can delete multiple rows at once by using a WHERE condition that matches more than one row, such as WHERE age > 65.
Some databases offer additional safety features like soft deletes (marking rows as deleted rather than removing them) or transaction management to undo mistakes.

Testing Your WHERE Condition Before Deleting

The safest way to delete records is to run a SELECT statement first using the exact same WHERE condition. This shows you which rows will be deleted before you actually delete them. For example, if you plan to run DELETE FROM orders WHERE status = 'cancelled';, first run SELECT * FROM orders WHERE status = 'cancelled'; to see the results.

This step takes 30 seconds and prevents accidental deletion of the wrong records. Once you confirm the SELECT results match what you want to delete, you can run the DELETE statement with confidence. Many experienced database administrators make this a habit for every deletion, regardless of how simple the condition seems.

Deleting Multiple Records at Once

A single DELETE statement can remove many rows if your WHERE condition matches multiple records. For instance, DELETE FROM employees WHERE department = 'sales'; removes every employee in the sales department. You can also use comparison operators like >, <, and != to target ranges of data.

Common examples include deleting old records by date (WHERE created_date < '2020-01-01'), removing inactive users (WHERE last_login IS NULL), or clearing test data (WHERE email LIKE '%test%'). The WHERE clause is flexible enough to handle most filtering needs, and you can combine multiple conditions with AND and OR.

Using Transactions to Undo Mistakes

If your database supports transactions, you can wrap your DELETE statement in a BEGIN and ROLLBACK to test the deletion before committing it permanently. The syntax varies by database: in MySQL and PostgreSQL, you write BEGIN; before the DELETE, then ROLLBACK; to undo or COMMIT; to make it permanent.

This approach is useful when you are deleting a large number of records and want to verify the count or check for unexpected side effects. After you run the DELETE inside a transaction, the database shows you how many rows were affected. If the number is wrong, you type ROLLBACK and the deletion never happens. If it is correct, you type COMMIT and the deletion becomes permanent.

Soft Deletes as an Alternative

Instead of permanently removing records, some applications use a soft delete — adding a column like is_deleted or deleted_at and setting it to true or the current timestamp rather than actually removing the row. This keeps the data in the database but marks it as inactive.

Soft deletes are useful when you need to preserve historical data, undo deletions later, or maintain referential integrity with other tables. Your application then filters out soft-deleted records in SELECT statements by adding WHERE is_deleted = false to every query. The trade-off is that your database stays larger and queries become slightly more complex, but you gain the ability to recover deleted data if needed.

Deleting Records with Foreign Key Constraints

If your table has foreign key relationships with other tables, attempting to delete a record may fail if child records still reference it. For example, if a customers table has a foreign key relationship with an orders table, you cannot delete a customer who has orders without first deleting or reassigning those orders.

When you encounter a foreign key error, you have two options: delete the child records first (in this case, the orders), or configure the foreign key to use CASCADE DELETE, which automatically removes child records when the parent is deleted. CASCADE DELETE is powerful but risky — use it only when you are certain you want child records to disappear along with the parent. Always check your database schema to understand these relationships before deleting.

Frequently Asked Questions

What happens if I run DELETE without a WHERE clause?

SQL will delete every row in the table. This is permanent in most databases unless you have a backup or can roll back a transaction. Always include a WHERE clause, and test it with SELECT first to avoid this mistake.

Can I undo a DELETE statement?

Not directly — once a DELETE is committed, it is permanent. Your only options are to restore from a backup or use a transaction with ROLLBACK if you catch the mistake immediately. This is why testing with SELECT first is so important.

How do I delete records based on a condition from another table?

You can use a subquery in the WHERE clause, such as DELETE FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'Canada'); This deletes all orders from Canadian customers. Test the subquery with SELECT first to confirm it returns the right customer IDs.

What is the difference between DELETE and TRUNCATE?

TRUNCATE removes all rows from a table much faster than DELETE, but it cannot use a WHERE clause and does not trigger foreign key constraints. Use DELETE when you need to remove specific rows; use TRUNCATE only when you want to empty an entire table and are certain no other tables depend on it.

Does deleting a record free up disk space?

Not immediately. Most databases mark the space as available for reuse but do not shrink the file. Some databases offer commands like VACUUM (PostgreSQL) or OPTIMIZE TABLE (MySQL) to reclaim space, but this is rarely necessary unless you delete a very large amount of data.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.