VMware ESXi and vSphere Cluster Management

How to Delete a Row from a MySQL Table

Learn how to safely remove one or more MySQL table rows with DELETE, WHERE conditions, SELECT previews, primary keys, affected-row checks, and LIMIT.

What the DELETE statement does

DELETE is a SQL data manipulation statement used to remove existing records from a table. A table is a database object that stores records in rows and attributes in columns. A row is one record, while a column is a named field in each record.

DELETE removes row data, but it does not remove the table itself. The table, its columns, and its structure remain available after rows are deleted.

The number of rows removed depends on the condition. A condition that identifies one unique row removes one row. A condition that matches several rows can remove all of those rows.

Basic DELETE syntax

DELETE FROM table_name
WHERE column_name operator value;

• DELETE FROM tells MySQL that rows will be removed.
• table_name is the table containing the target rows.
• WHERE introduces the condition that limits which rows are affected.
• column_name is the column MySQL checks.
• operator compares the column value with another value, such as =, <, or >.
• value is the value used by the comparison.

A condition is the logical test that determines whether a row matches. The = symbol is a comparison operator that means “equals.” Text values are written as SQL string literals, normally inside single quotes.

Example table

Assume a table named testtb contains these records:

Preview a row before deleting it

Before changing data, use SELECT with the condition you plan to use in DELETE. This lets you inspect the exact matching set.

SELECT * FROM testtb
WHERE surname = 'Jones';

In this sample, the query returns the John Jones record. Since the surname condition matches one row in the current data, it is suitable for demonstrating a targeted deletion. In a real database, confirm that the condition is unique enough for your intended operation.

Delete one matching row

DELETE FROM testtb
WHERE surname = 'Jones';

MySQL evaluates the WHERE condition for each row and removes the row whose surname value is the string literal 'Jones'. The table itself remains in the database.

Verify the deletion

Run the original inspection query again, or review the complete table:

SELECT * FROM testtb;

The remaining data should look like this:

MySQL reports an affected-row count after a DELETE. Affected rows are the records changed or removed by a statement. For the example above, the expected count is one. A count of zero means that no row matched the condition; a count greater than one means that the condition was not unique in the stored data.

The danger of leaving out WHERE

DELETE FROM testtb;

A DELETE statement without a WHERE clause applies to every row in the named table. It empties the table's data but preserves the table structure. This is very different from deleting the table itself, but it can still cause serious data loss.

When a WHERE condition matches multiple rows

A condition based on a non-unique value can match several records. For example:

SELECT * FROM testtb
WHERE name = 'John';

This returns both John records in the sample. If you then run the following statement, both matching rows may be removed:

DELETE FROM testtb
WHERE name = 'John';

Use a primary key or another unique identifier when the goal is to delete one exact row. A primary key is a unique column, or combination of columns, that identifies one row.

DELETE FROM testtb
WHERE id = 42;

This production-style example assumes that testtb has an id primary-key column and that 42 identifies the intended record. If a primary key is unavailable, combine conditions that make the target precise, and preview the complete matching set first.

Using DELETE with LIMIT

MySQL supports LIMIT with DELETE to cap the number of matching rows removed:

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

LIMIT restricts how many rows the statement can affect. However, LIMIT 1 alone does not guarantee which matching row is selected when several rows satisfy the condition. It is not a replacement for a primary-key condition or another deterministic selection method. Use a unique identifier whenever a particular row must be removed.

Transactions for higher-risk deletions

A transaction groups changes so they can be committed or rolled back according to the database configuration and storage engine. A rollback can undo an uncommitted deletion.

START TRANSACTION;

SELECT * FROM testtb
WHERE id = 42;

DELETE FROM testtb
WHERE id = 42;

-- Inspect the result, then choose one:
COMMIT;
-- or:
ROLLBACK;

Do not assume that every environment can roll back every DELETE. Confirm that the table uses a transactional storage engine and that the transaction is still uncommitted. Backups remain important for recovery from committed or otherwise irreversible changes.

Troubleshooting DELETE statements

All rows were removed unexpectedly

The likely cause is a DELETE statement without a WHERE clause. If the deletion occurred inside an uncommitted transaction, use ROLLBACK immediately. Otherwise, restore the affected data from a suitable backup or recovery process. Always preview with SELECT and consider a transaction for high-risk changes.

More than one row was deleted

The condition probably matched duplicate or non-unique values. Run SELECT with the same condition to inspect every match. Refine the condition with additional columns or use a primary-key value.

No rows were deleted

No stored value matched the condition. Check spelling, whitespace, the actual values returned by SELECT, and case behavior under the table's collation. Also confirm that you selected the correct database and table.

MySQL rejects the statement

Check the exact server error. Common causes include incorrect syntax, missing DELETE privileges, foreign-key restrictions, and safe-update settings. If another table has a foreign key referencing the row, investigate those referencing records before deleting the parent record. Some safe-update configurations also require an indexed or key-based condition.

DELETE checklist

1. Confirm the database and table name.
2. Run SELECT to inspect the rows that the planned condition matches.
3. Prefer a primary-key or other unique identifier for one-row deletion.
4. Check the expected match count and the data values.
5. Run DELETE with the reviewed WHERE clause.
6. Check MySQL's affected-row count.
7. Run SELECT again to verify that the intended row is gone and other rows remain.
8. Use a transaction and an appropriate recovery plan for high-risk changes.

For a concise reference, see the MySQL row deletion guide.