Delete Single/Specific row in MySQL table

Delete a Single Specific Row from a MySQL Table

To delete one specific row from a MySQL table, use a DELETE statement with a WHERE condition that uniquely identifies the row. A primary key or another unique column is usually the safest choice.

Before running the delete query, test the same WHERE condition with a SELECT statement. This confirms that the condition matches only the intended row.

You can then run the DELETE query. In MySQL, LIMIT 1 can provide an additional safeguard, but it should not replace a precise and unique WHERE condition.

MySQL DELETE Syntax for One Specific Row

Following is the syntax to delete a single row in a table:

DELETE FROM table_name WHERE selection_criteria LIMIT 1;

Replace:

• table_name with the name of the table.
• selection_criteria with a condition that identifies the required row.
• LIMIT 1 with nothing if the condition already uses a primary key or unique value and you do not need the additional limit.

The WHERE clause is essential. Running DELETE FROM table_name without a WHERE clause deletes every row in the table.

Delete One MySQL Row by Primary Key

The preferred method is to delete the row by its primary key. Because a primary key value is unique, the condition can match no more than one row.

DELETE FROM table_name
WHERE primary_key_column = value;

For example, the following query deletes the student whose unique id is 4:

DELETE FROM students
WHERE id = 4;

Example: Delete Only One Row from the students Table

Consider the following students table.

Suppose we want to delete the row for Ruma, whose age is 10. The id column is the primary key of this table, so its value is the most reliable selection criterion.

For Ruma’s row, the selection criterion is id=4.

Verify the MySQL Row Before Deleting It

Before deleting the row, run a SELECT statement with the same condition:

SELECT *
FROM students
WHERE id = 4;

The query should return only the row that you intend to delete.

Run the MySQL DELETE Query

SQL Query to delete just this row would be:

DELETE FROM students WHERE id=4 LIMIT 1;

Execute the query.

The row has been deleted. MySQL normally reports the number of affected rows, which should be 1 for this query.

Confirm That the MySQL Row Was Deleted

Run another SELECT statement to verify that no row with id=4 remains:

SELECT *
FROM students
WHERE id = 4;

The query should return an empty result.

Safely Delete a MySQL Row Inside a Transaction

For an InnoDB table, you can perform the deletion inside a transaction. This lets you inspect the result and use ROLLBACK if the wrong row was selected.

START TRANSACTION;

SELECT *
FROM students
WHERE id = 4;

DELETE FROM students
WHERE id = 4;

SELECT ROW_COUNT() AS deleted_rows;

COMMIT;

Use ROLLBACK instead of COMMIT when the result is not correct:

ROLLBACK;

A rollback is possible only before the transaction has been committed and only when the storage engine supports transactions.

Delete One MySQL Row When Values Are Duplicated

A condition based on non-unique values can match several rows. For example, multiple students could have the same name and age:

DELETE FROM students
WHERE name = 'Ruma'
  AND age = 10
LIMIT 1;

This statement deletes no more than one matching row, but when several rows satisfy the condition, it may not be clear which duplicate will be removed. First retrieve the matching records and identify the required row by a primary key:

SELECT id, name, age
FROM students
WHERE name = 'Ruma'
  AND age = 10;

After finding the correct id, delete that row using its unique value. Do not rely on LIMIT 1 to choose between duplicate rows unless deleting any one of them is acceptable.

Delete a MySQL Row Using Multiple Conditions

When a table does not have a suitable unique identifier, combine enough conditions to distinguish the required row.

DELETE FROM students
WHERE name = 'Ruma'
  AND age = 10
  AND class_name = 'Grade 5'
LIMIT 1;

Verify the combined condition with SELECT before using it in a delete statement. For tables that frequently require row-level updates and deletions, adding an appropriate primary key is safer than repeatedly depending on combinations of non-unique values.

MySQL DELETE, TRUNCATE, and DROP Differences

Use the operation that matches what you intend to remove:

MySQL statement	What it removes	Use for one specific row?
DELETE FROM table WHERE condition	Rows matching the condition	Yes
DELETE FROM table	All rows while retaining the table	No
TRUNCATE TABLE table	All rows while retaining the table structure	No
DROP TABLE table	The table, its data, and its definition	No

For a single-row deletion, use DELETE with a precise WHERE clause. Do not use TRUNCATE or DROP TABLE.

Common Problems When Deleting a Specific MySQL Row

• Missing WHERE clause: The statement deletes every row in the table.
• Non-unique condition: The statement can delete several matching records unless it is limited.
• LIMIT used with duplicate rows: Only one row is deleted, but it might not be the duplicate you intended to remove.
• Foreign key restriction: MySQL can reject the deletion when another table references the row and the foreign key does not permit the delete.
• Automatic cascading deletion: A foreign key configured with ON DELETE CASCADE can also delete related child rows.
• Wrong data type comparison: Quote string and date values correctly, while numeric values generally do not require quotes.
• Slow row lookup: A condition on an indexed primary key or unique column is generally more efficient than scanning an unindexed column.

MySQL Single-Row DELETE FAQs

How do I delete a particular row from a MySQL table?

Use DELETE FROM table_name WHERE condition. The condition should use a primary key or another unique value that identifies the particular row.

How do I ensure that MySQL deletes only one row?

Use a primary key or unique column in the WHERE clause. MySQL also supports LIMIT 1 for a single-table delete, but a unique condition is more reliable because it identifies the exact row.

What happens if I omit WHERE from a MySQL DELETE statement?

MySQL deletes all rows from the table. The table itself remains, but its records are removed.

Can I recover a deleted MySQL row?

You can use ROLLBACK when the deletion was made in an uncommitted transaction on a transactional table. After the deletion is committed, recovery normally requires a backup, binary logs, or another recovery mechanism.

Why does MySQL refuse to delete a specific row?

A foreign key may reference the row from another table. Inspect the related constraints and decide whether the child row should be retained, deleted first, or handled through an appropriate foreign key action.

MySQL Specific-Row DELETE Editorial QA Checklist

• Confirm that every single-row delete example includes a WHERE clause.
• Verify that the example condition uses a primary key, unique value, or sufficiently specific combination of columns.
• Run the equivalent SELECT query before the DELETE query in each practical workflow.
• Do not describe LIMIT 1 as a substitute for uniquely identifying the intended row.
• Check whether foreign key constraints or cascading actions affect related records.
• Confirm that transaction and rollback guidance applies to the table’s storage engine.
• Compare syntax details with the MySQL DELETE statement documentation.

The safest pattern is to identify the row by its primary key, verify it with SELECT, delete it with the same condition, and confirm that exactly one row was affected.