How to Delete Rows From a Table in SQL

What the DELETE statement does

The DELETE statement removes rows from a table in a database. It is the SQL command you use when you want to get rid of data permanently. Unlike hiding or archiving data, DELETE actually takes it out of the table — though most databases let you undo the action if you catch it when ready.

The critical thing to understand: DELETE without conditions removes every row in the table. This is why most people accidentally delete everything once, and why databases usually require you to be explicit about which rows to remove.

Key Takeaways

The basic DELETE syntax is DELETE FROM table_name WHERE condition; — the WHERE part tells the database which rows to remove.
Forgetting the WHERE clause deletes all rows in the table, which is why you should test your condition on a SELECT statement first.
Most databases let you undo a DELETE if you catch it before you commit the transaction, but this window closes once you save.
You can delete rows based on a single value, a range, or multiple conditions joined with AND or OR.
Deleting rows that other tables depend on can cause errors if foreign key constraints are set up — the database will refuse the deletion to protect data integrity.

The basic DELETE syntax and how to use it safely

The simplest DELETE statement has three parts: the DELETE FROM keyword, the table name, and a WHERE clause that specifies which rows to remove. Here is the structure:

DELETE FROM table_name WHERE condition;

The WHERE clause is what keeps you from deleting the entire table by accident. Without it, every row disappears. The condition can be as straightforward as matching a single value — for example, DELETE FROM customers WHERE customer_id = 5; removes only the customer with ID 5. The condition can also be more complex, using operators like greater than (>), less than (<), or LIKE for partial matches.

Before you run a DELETE statement on real data, test your WHERE condition first using a SELECT statement with the same condition. If SELECT * FROM customers WHERE customer_id = 5; shows you exactly the rows you want to remove, then you can run the DELETE version with confidence.

Deleting rows based on multiple conditions

You can combine conditions using AND and OR to target specific rows more precisely. Use AND when all conditions must be true, and OR when any condition can be true.

For example, DELETE FROM orders WHERE customer_id = 5 AND order_date < '2023-01-01'; removes only orders from customer 5 that are older than January 1, 2023. If you wanted to delete orders from either customer 5 or customer 7, you would write DELETE FROM orders WHERE customer_id = 5 OR customer_id = 7;

You can also use IN to match multiple values in one condition: DELETE FROM orders WHERE customer_id IN (5, 7, 9); removes orders from customers 5, 7, and 9 in a single statement. This is cleaner than writing three separate OR conditions.

What happens when foreign keys block a deletion

Many databases link tables together using foreign keys — rules that say "this row in one table depends on that row in another table." If you try to delete a row that other rows depend on, the database refuses the deletion and returns an error.

For example, if you have a customers table and an orders table, and each order is linked to a customer, you cannot delete a customer who still has orders. The database protects the orders from becoming orphaned — pointing to a customer that no longer exists.

To delete a customer with existing orders, you must first delete all their orders, then delete the customer. Some databases offer a CASCADE option that deletes dependent rows automatically, but this is dangerous and usually only used in specific situations. Check your database documentation or ask your database administrator before using CASCADE.

Undoing a deletion before it is permanent

Most databases use transactions — a way to group multiple actions together and either save them all or undo them all. If you run a DELETE statement and when ready realize you made a mistake, you can undo it with a ROLLBACK command before you commit.

The exact steps depend on your database software. In SQL Server, you can type ROLLBACK; to undo recent changes. In MySQL, you may need to set SET autocommit = 0; first to prevent automatic saving. PostgreSQL works similarly. Once you run COMMIT or close the connection, the deletion is permanent and cannot be undone through the database itself.

This is why testing your WHERE condition on a SELECT statement first is so important — it is faster and safer than relying on ROLLBACK.

Deleting rows based on ranges and dates

You often need to delete data older than a certain date or outside a certain range. Use comparison operators like < (less than), > (greater than), <= (less than or equal), and >= (greater than or equal).

For example, DELETE FROM logs WHERE log_date < '2022-01-01'; removes all log entries from before 2022. You can combine range conditions with AND: DELETE FROM transactions WHERE amount > 1000 AND transaction_date < '2023-06-01'; removes large transactions from the first half of 2023.

When working with dates, make sure your date format matches what the database expects. Most databases use YYYY-MM-DD format. If your dates are stored as text instead of date columns, the comparison may not work correctly — contact your database administrator if the deletion does not return the rows you expected.

Common mistakes and how to avoid them

The most common mistake is forgetting the WHERE clause entirely. A statement like DELETE FROM customers; with no condition removes every row in the customers table. Always include WHERE unless you genuinely want to empty the entire table.

The second mistake is testing your condition wrong. A SELECT statement that returns 100 rows does not mean your DELETE will remove 100 rows if you wrote the conditions differently. Copy the exact WHERE clause from your SELECT test into your DELETE statement.

The third mistake is deleting rows you did not intend to because your condition was too broad. For example, DELETE FROM orders WHERE customer_id = 5; removes all orders from customer 5, not just one. If you only wanted to remove a specific order, add more conditions: DELETE FROM orders WHERE customer_id = 5 AND order_id = 42;

Finally, do not assume you can delete a row if you do not understand what depends on it. Ask your database administrator or check the table structure before deleting rows that might be referenced elsewhere.

Frequently Asked Questions

Can I delete a row and get it back later?

If you run ROLLBACK before you commit, yes. Once you commit or close the connection, the deletion is permanent unless your database has backups. Some organizations keep daily backups and can restore deleted data, but this takes time and may not be possible for recent deletions. Always test your WHERE condition first.

What is the difference between DELETE and DROP?

DELETE removes rows from a table but keeps the table structure intact. DROP removes the entire table, including its structure and all data. You use DELETE to remove specific data and DROP to remove the table itself. DROP is much more destructive.

Why does my DELETE statement return an error about foreign keys?

Another table has rows that depend on the row you are trying to delete. You must delete the dependent rows first, or ask your database administrator if CASCADE deletion is set up. Check which tables reference the one you are deleting from.

Can I delete rows based on data in another table?

Yes, using a subquery. For example, DELETE FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'Canada'); deletes all orders from Canadian customers. Write and test the SELECT subquery first to make sure it returns the right customer IDs.

How do I know if my DELETE worked?

Most databases tell you how many rows were deleted when the statement finishes. If it says "0 rows affected," your WHERE condition did not match any rows. Run the same SELECT statement to see what rows actually exist in the table.

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.