Remove Rows from a MySQL Table with DELETE

Learn how to safely remove one or more rows from a MySQL table with DELETE, WHERE, LIMIT, primary keys, verification queries, and transactions.

The MySQL DELETE statement removes existing rows from a table. A row is one record, while a table is the database object that stores rows and columns.

DELETE changes the table's data, not its definition. The table, its columns, and its structure remain after rows are removed. This differs from dropping a table, which removes the table object itself.

Basic DELETE syntax

DELETE FROM table_name
WHERE condition;

table_name is the table containing the rows to remove. The WHERE clause contains a condition: an expression that tests whether each row should be deleted. Only rows for which the condition is true are affected.

Inspect rows before deleting

Before deleting anything, use SELECT to inspect the table and locate the intended record:

SELECT * FROM testtb;

You can preview exactly which rows a DELETE statement would target by using the same condition in a SELECT query:

SELECT *
FROM testtb
WHERE surname = 'Jones';

Review the result before running the DELETE statement. This is one of the simplest and most effective ways to prevent accidental deletion.

Delete a row by a text value

Suppose testtb contains the columns name, surname, and year. To remove rows whose surname is Jones, write:

DELETE FROM testtb
WHERE surname = 'Jones';

'Jones' is a string literal, which means a text value written in quotes in a SQL expression. Text values should normally be enclosed in single quotes.

This condition removes every row whose surname value matches Jones. It does not necessarily remove only one row, because more than one person can have the same surname.

Before-and-after example

Both rows with the surname Jones are removed because both satisfy the condition.

Verify the deletion

After DELETE completes, query the table again:

SELECT * FROM testtb;

You can also verify that no Jones rows remain:

SELECT *
FROM testtb
WHERE surname = 'Jones';

If the result is empty, no rows currently match that condition. MySQL also reports an affected-row count. For DELETE, this normally indicates how many rows were deleted by the statement.

Why the WHERE clause matters

Omitting WHERE deletes every row:

DELETE FROM testtb;

Important: DELETE FROM testtb; removes the contents of the table, not the table definition. The table still exists and can receive new rows. Use a precise WHERE clause whenever you intend to remove selected records.

Deleting all rows is different from dropping the table. DELETE preserves the table object, while DROP TABLE removes the table and its definition. If you only want to empty a table, also consider the separate behavior of TRUNCATE TABLE.

Conditions that match multiple rows

A DELETE condition can match zero, one, or many rows. Conditions based on values that are not unique can remove more rows than intended:

DELETE FROM customers
WHERE last_name = 'Jones';

If several customers have that last name, all of them qualify. When only one row should be removed, use a primary key or another unique identifier whenever available. A primary key is a column, or set of columns, that uniquely identifies each row.

DELETE FROM testtb
WHERE id = 42;

This is safer because the condition identifies the row by its unique ID. You can preview it first:

SELECT *
FROM testtb
WHERE id = 42;

For background on unique row identifiers, see Primary Keys.

Restrict a deletion with LIMIT

In supported simple single-table DELETE statements, LIMIT restricts the maximum number of qualifying rows that can be deleted:

DELETE FROM testtb
WHERE surname = 'Jones'
LIMIT 1;

This removes at most one matching row. However, if several rows qualify, LIMIT by itself does not identify which particular row should be removed. Without a deterministic ordering and a unique selector, the chosen row may not be predictable.

Use a unique key when the intention is to remove a specific record:

DELETE FROM customers
WHERE customer_id = 42;

Use LIMIT 1 as a safeguard against deleting multiple matches, not as a substitute for a primary key or unique condition. See Limit Clause for more about limiting query results.

A safe deletion workflow

Identify the intended table and record.
Run SELECT with the exact WHERE predicate that will be used by DELETE.
Check the returned rows and confirm that every displayed row is intended for removal.
Run the DELETE statement.
Review MySQL's affected-row count.
Run SELECT again to verify the remaining data.

For transactional storage engines and workflows that support transactions, test the deletion before committing it:

START TRANSACTION;

DELETE FROM testtb
WHERE id = 42;

ROLLBACK;

ROLLBACK undoes the uncommitted deletion. If the result is correct, use COMMIT instead of ROLLBACK:

START TRANSACTION;

DELETE FROM testtb
WHERE id = 42;

COMMIT;

A transaction is a unit of database work that can be committed or rolled back when supported by the storage engine and the surrounding workflow. Confirm your transaction settings before relying on rollback.

Troubleshooting DELETE statements

All rows were removed unexpectedly

The likely cause is a DELETE statement without a WHERE clause. Use ROLLBACK only if the deletion is still inside an uncommitted transaction. Otherwise, restoration generally requires an available backup or another recovery process.

More than one row was deleted

The condition probably matched duplicate or non-unique values, such as a surname. Run the corresponding SELECT query first and use a primary key or other unique condition for future deletions.

No rows were deleted

No record may match the condition. Check the stored values and the column's data type. Differences in spelling, whitespace, collation behavior, or comparison values can affect matching.

The wrong row was removed with LIMIT 1

Several rows qualified, and no unique identifier selected a particular row. Use a primary key or unique key instead of relying on LIMIT to choose a specific record.

Key points

DELETE FROM table_name WHERE condition; removes rows that satisfy the condition.
The WHERE clause controls which rows are affected.
Always preview the exact condition with SELECT before deleting.
A non-unique condition can delete multiple rows.
Use a primary key or unique identifier for intentional single-row deletion.
DELETE without WHERE removes every row but leaves the table definition intact.
Check the affected-row count and verify the table after deletion.
Use a transaction when supported and appropriate so an uncommitted mistake can be rolled back.