How to Remove Rows From a SQL Table

The DELETE statement removes rows from a SQL table

To delete rows from a table in SQL, you use the DELETE statement with a WHERE clause that specifies which rows to remove. The WHERE clause is critical — without it, the DELETE statement will erase every row in the table. Most SQL databases (MySQL, PostgreSQL, SQL Server, SQLite) use the same basic syntax, though some have slight variations in how they handle transactions and confirmations.

The simplest DELETE statement looks like this:

DELETE FROM table_name WHERE condition;

For example, if you have a customers table 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 wrote DELETE FROM customers without the WHERE clause, you would delete all customers in the table — a mistake that is hard to undo if you do not have a backup.

Key Takeaways

Always include a WHERE clause in your DELETE statement, or you will erase every row in the table.
Test your WHERE condition with a SELECT statement first to confirm you are targeting the right rows before you delete them.
Some databases require you to commit the transaction after DELETE, while others do it automatically depending on your settings.
Deleted rows cannot be recovered unless you have a backup or transaction logs, so double-check before you execute the statement.

Write and test your WHERE clause before deleting

The safest way to delete rows is to write your WHERE clause as a SELECT statement first. This shows you exactly which rows will be affected before you actually delete them. For example, if you want to delete all orders placed before January 1, 2020, write this first:

SELECT * FROM orders WHERE order_date < '2020-01-01';

Run this query and look at the results. If the rows shown are the ones you want to delete, change SELECT * to DELETE and run it again:

DELETE FROM orders WHERE order_date < '2020-01-01';

This two-step approach catches mistakes before they happen. A common error is using the wrong comparison operator — for instance, writing > instead of < — which would delete the opposite set of rows from what you intended.

Delete rows that match 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, to delete all inactive customers who have not made a purchase in over a year:

DELETE FROM customers WHERE status = 'inactive' AND last_purchase < '2023-01-01';

To delete customers from either New York or California:

DELETE FROM customers WHERE state = 'NY' OR state = 'CA';

You can also use IN to match any value in a list. To delete customers with IDs 5, 12, and 18:

DELETE FROM customers WHERE id IN (5, 12, 18);

When you combine multiple conditions, test with SELECT first. It is easy to accidentally delete more rows than intended when using OR, because OR is less restrictive than AND.

Understand how your database handles deletions

Different SQL databases behave slightly differently after a DELETE statement. In MySQL and SQLite, the statement usually takes effect immediately. In SQL Server and PostgreSQL, the deletion is part of a transaction — a group of changes that either all succeed or all fail together. If you are using a transaction, you must run COMMIT to make the deletion permanent, or ROLLBACK to undo it.

If you are not sure whether your database is in auto-commit mode, check your database settings or ask your database administrator. In many tools like MySQL Workbench or SQL Server Management Studio, you can see whether auto-commit is on or off in the connection settings. If auto-commit is off and you delete rows without committing, the changes stay in memory but are not saved to disk — and another user connecting to the database will not see the deletion.

Delete all rows in a table (when you really mean to)

If you actually do want to remove every row from a table while keeping the table structure itself, use DELETE without a WHERE clause:

DELETE FROM table_name;

This is rarely what you want, but it is sometimes necessary when you need to clear out test data or reset a table. Before you run this, make sure you have a backup or that you are working in a test environment. Many teams require a second person to review a DELETE statement with no WHERE clause before it runs in production.

If you want to remove the table entirely — including its structure and any indexes — use DROP TABLE instead. DROP TABLE is even more destructive and should only be used when you are certain the table is no longer needed.

Recover from accidental deletions

If you delete rows by mistake, your options depend on your database and whether you have backups. Most production databases keep transaction logs that record every change, and a database administrator can restore deleted rows from a specific point in time. This process takes time and may require taking the database offline, so it is not a quick fix.

The best protection is to take regular backups and to test DELETE statements in a development environment before running them on live data. Some teams also use soft deletes — adding an is_deleted column and marking rows as deleted rather than removing them — so that data can be recovered if needed.

Frequently Asked Questions

What happens if I delete a row that other tables reference?

This depends on your foreign key constraints. If a foreign key has ON DELETE CASCADE set, deleting a row will automatically delete all related rows in other tables. If it has ON DELETE RESTRICT, the database will prevent the deletion and show an error. Check your table structure to understand which behavior is in place before you delete.

Can I delete rows from multiple tables at once?

You cannot delete from multiple tables in a single DELETE statement in most databases. You must run separate DELETE statements for each table, usually starting with the table that has the foreign key reference. Some databases allow you to use a JOIN in the DELETE statement to target rows based on conditions in another table.

How do I know how many rows were deleted?

Most SQL databases return a count of affected rows after a DELETE statement completes. In MySQL, you can check the number of rows deleted in the message that appears after the query runs. In SQL Server, use @@ROWCOUNT immediately after the DELETE to see the count. In PostgreSQL, the statement returns the number of rows deleted.

Is DELETE the same as TRUNCATE?

No. DELETE removes rows one at a time and can be rolled back in a transaction. TRUNCATE removes all rows at once and is faster, but cannot be rolled back in most databases and does not trigger delete triggers. Use TRUNCATE only when you want to clear an entire table and are certain you do not need the data.

Can I delete rows based on a condition in a different table?

Yes, using a subquery or JOIN. For example, to delete all orders from customers in California: DELETE FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE state = 'CA'); Test the subquery with SELECT first to make sure it returns the right customer IDs before you run the DELETE.

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.