How to Delete Rows in MySQL

Galaxy Glossary

How do I safely delete specific rows from a MySQL table?

DELETE removes one or many rows from a MySQL table based on an optional condition.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why use DELETE instead of TRUNCATE?

DELETE lets you remove selected rows while TRUNCATE wipes the whole table. Use DELETE when you need precision and want to retain table structure, permissions, and auto-increment values.

What is the basic DELETE syntax?

Run DELETE FROM table_name WHERE condition;. Omitting WHERE deletes every row, so always double-check the filter before execution.

How do I delete a single customer?

Target the row with a unique identifier. For example: DELETE FROM Customers WHERE id = 17;. Only the customer with id 17 disappears; other data remains intact.

How can I delete orders older than a year?

Combine date logic with DELETE: DELETE FROM Orders WHERE order_date < NOW() - INTERVAL 1 YEAR;. This cleans historical orders while keeping recent transactions.

How do I safely test DELETE?

First run the same SELECT query with your WHERE clause to preview affected rows. After confirming, switch SELECT to DELETE.

Can I delete from multiple tables at once?

Yes—use DELETE t1, t2 FROM ... JOIN ... WHERE .... MySQL allows multi-table deletes to keep child tables in sync, reducing round-trips.

How do I delete rows referenced by foreign keys?

Either set ON DELETE CASCADE in the foreign key or delete child rows first. Attempting to delete parent rows without handling children triggers a constraint error.

Best practices for DELETE

1) Always back up data. 2) Use transactions (START TRANSACTION ... COMMIT) so you can ROLLBACK on mistakes. 3) Batch large deletes with LIMIT to avoid long locks and replication lag.

Is there a way to recover deleted rows?

Only from backups or binary logs. MySQL lacks an undo command after COMMIT. Plan ahead by enabling point-in-time recovery.

Why How to Delete Rows in MySQL is important

How to Delete Rows in MySQL Example Usage


-- Remove unpaid orders older than 30 days and their items
START TRANSACTION;
DELETE Orders, OrderItems
FROM Orders
JOIN OrderItems ON OrderItems.order_id = Orders.id
WHERE Orders.total_amount = 0
  AND Orders.order_date < NOW() - INTERVAL 30 DAY;
COMMIT;

How to Delete Rows in MySQL Syntax


DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       FROM table_name
       [PARTITION (p0, p1, ...)]
       [USING table_references]
       [WHERE condition]
       [ORDER BY expr]
       [LIMIT row_count];

# Example variants
-- Delete one customer by primary key
DELETE FROM Customers WHERE id = 42;

-- Delete out-of-stock products quickly, skipping errors
DELETE QUICK IGNORE FROM Products WHERE stock = 0;

-- Multi-table delete removing orphaned order items
DELETE OrderItems FROM OrderItems
JOIN Products ON Products.id = OrderItems.product_id
WHERE Products.id IS NULL;

Common Mistakes

Forgetting the WHERE clause. Running "DELETE FROM Customers;" wipes the entire table. Always test with SELECT first and verify the row count.
Deleting parent rows before children when foreign keys lack ON DELETE CASCADE. This raises constraint errors. Delete children first or enable cascading.

Frequently Asked Questions (FAQs)

Can I delete and reset AUTO_INCREMENT?

DELETE removes rows but leaves AUTO_INCREMENT unchanged. Use ALTER TABLE ... AUTO_INCREMENT = 1 after deleting all rows if you need to reseed.

Does DELETE free disk space?

InnoDB marks pages as reusable; actual file size stays. Run OPTIMIZE TABLE or TRUNCATE to reclaim physical space.

How do I limit deletes to avoid locks?

Append ORDER BY pk, LIMIT 1000 inside a loop. Smaller batches shorten lock time and let replication keep up.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie