SQL DELETE Command in MySQL

The SQL DELETE command removes rows from a table. Use a WHERE clause when only selected records should be deleted.

DELETE FROM student
WHERE mark < 60;

This removes only students whose mark value is below 60.

SELECT id, name, mark
FROM student
WHERE mark < 60;

SQL DELETE Syntax Top ↑

DELETE FROM table_name
WHERE condition;

The WHERE condition decides which rows are removed.

Example: delete one student by ID.

DELETE FROM student
WHERE id = 10;

When an ID uniquely identifies a record, this normally removes at most one row.

Preview Rows before Running DELETE Top ↑

A practical safety habit is to test the condition with SELECT first.

SELECT id, name, class, mark
FROM student
WHERE class = 'Four';

After confirming the result, use the same condition with DELETE:

DELETE FROM student
WHERE class = 'Four';

This is safer than writing a destructive condition and executing it without first checking which records match.

DELETE with Different WHERE Conditions Top ↑

Delete Using a Numeric Comparison Top ↑

DELETE FROM student
WHERE mark < 60;

Delete Using AND Top ↑

DELETE FROM student
WHERE class = 'Four'
  AND mark < 60;

Delete Using IN Top ↑

DELETE FROM student
WHERE class IN ('Four', 'Five');

See AND / OR conditions and IN for deeper filtering examples.

Delete All Rows from a Table Top ↑

A DELETE statement without a WHERE clause removes every row while leaving the table structure in place.

DELETE FROM student;

MySQL also supports TRUNCATE when the goal is specifically to empty a whole table:

TRUNCATE TABLE student;

DELETE and TRUNCATE are not interchangeable in every situation. Their transaction, trigger, foreign-key and AUTO_INCREMENT behavior differs.

Delete Linked Records from Multiple Tables Top ↑

MySQL supports multi-table DELETE statements. Suppose student contains one student record and student_fee contains related fee records for the same ID.

To remove matching records from both tables for student ID 2:

DELETE student, student_fee
FROM student
INNER JOIN student_fee
    ON student.id = student_fee.id
WHERE student.id = 2;

This removes the matching row from student and all matching rows from student_fee.

To remove all rows that match in both tables:

DELETE student, student_fee
FROM student
INNER JOIN student_fee
    ON student.id = student_fee.id;

Delete Records from Three Linked Tables Top ↑

The following MySQL example works with three linked tables used by the Plus2net country, state and city dropdown example.

DELETE FROM plus2_country, plus2_state, plus2_city
USING plus2_country
INNER JOIN plus2_state
    ON plus2_country.country_code = plus2_state.country_code
INNER JOIN plus2_city
    ON plus2_state.state_id = plus2_city.state_id
WHERE plus2_country.country_code = 'CAN';

The query removes Canada from plus2_country, its matching states from plus2_state, and matching cities from plus2_city.

Delete Rows That Do Not Exist in Another Table Top ↑

Suppose we want to remove students who have no matching row in student_fee.

Using LEFT JOIN Top ↑

DELETE student
FROM student
LEFT JOIN student_fee
    ON student_fee.id = student.id
WHERE student_fee.id IS NULL;

See the LEFT JOIN tutorial for the matching logic.

Using NOT EXISTS Top ↑

DELETE FROM student
WHERE NOT EXISTS (
    SELECT 1
    FROM student_fee
    WHERE student_fee.id = student.id
);

NOT EXISTS is often a clear choice for this type of anti-match condition. See SQL subqueries.

Using NOT IN Top ↑

DELETE FROM student
WHERE id NOT IN (
    SELECT id
    FROM student_fee
    WHERE id IS NOT NULL
);

DELETE with ORDER BY and LIMIT in MySQL Top ↑

For a single-table DELETE, MySQL can combine DELETE with ORDER BY and LIMIT.

DELETE FROM student
WHERE mark < 40
ORDER BY id
LIMIT 5;

This removes at most five matching rows, starting with the lowest IDs according to the stated ordering.

Using ORDER BY is important when the specific rows removed by LIMIT must be predictable. See ORDER BY and LIMIT.

Foreign Keys and Cascading Deletes Top ↑

A DELETE can fail when another table references the row through a foreign key.

Depending on the schema, the relationship can be configured to reject the delete or to remove related child rows automatically with ON DELETE CASCADE.

DELETE and Transactions Top ↑

For transactional MySQL tables such as InnoDB, DELETE is a data-modification statement that can participate in a transaction.

START TRANSACTION;

DELETE FROM student
WHERE id = 10;

ROLLBACK;

Before the transaction is committed, a rollback can undo the DELETE. Once committed, recovery requires another data source such as a backup or application history.

This differs from MySQL DDL operations such as normal TRUNCATE and DROP TABLE, which have implicit-commit behavior.

DELETE vs TRUNCATE vs DROP Top ↑

DELETE Top ↑

DELETE FROM student
WHERE class = 'Four';

Deletes matching rows and keeps the table.

TRUNCATE Top ↑

TRUNCATE TABLE student;

Empties the table. In MySQL, TRUNCATE also resets AUTO_INCREMENT for the table and has DDL-like implicit-commit behavior.

DROP TABLE Top ↑

DROP TABLE student;

Removes the table itself, including its stored rows and definition.

Run a DELETE Query using PHP PDO Top ↑

When the condition contains an external value, use a prepared PDO statement instead of concatenating the value into SQL.

<?php
require 'config.php';

$id=10;

$sql="DELETE FROM student
      WHERE id=:id";

$stmt=$dbo->prepare($sql);
$stmt->bindValue(':id',$id,PDO::PARAM_INT);
$stmt->execute();

$deleted=$stmt->rowCount();

echo 'Rows deleted: '.$deleted;

See the PHP PDO DELETE tutorial for input validation, POST confirmation, transactions and application-level safety.

Count the Number of Deleted Rows Top ↑

MySQL reports the number of rows affected by DELETE. In PDO, rowCount() can be used after the DELETE statement:

$deleted=$stmt->rowCount();
echo 'Rows deleted: '.$deleted;

At the MySQL level, affected-row information is also covered in MySQL affected rows.

Common SQL DELETE Problems Top ↑

Forgetting the WHERE Clause Top ↑

DELETE FROM student; is valid SQL and removes every row. Always verify whether a condition is required.

Deleting before Previewing the Condition Top ↑

For important data, run SELECT with the same WHERE clause first so you can see exactly which rows match.

Using NOT IN with NULL Values Top ↑

If the subquery can return NULL, NOT IN may not behave as expected. Filter NULLs explicitly or consider NOT EXISTS.

Assuming DELETE Resets AUTO_INCREMENT Top ↑

Deleting rows does not normally reset the table's AUTO_INCREMENT sequence. TRUNCATE has different behavior in MySQL.

Ignoring Foreign Keys Top ↑

Related child rows can block a DELETE or be removed automatically depending on the foreign-key action defined in the schema.

Concatenating User Input into DELETE SQL Top ↑

Use prepared statements for external values. Prepared statements do not replace validation, authorization or CSRF protection in an application.

Assuming Every Database Supports MySQL Multi-Table DELETE Top ↑

The multi-table examples on this page use MySQL syntax. Other database systems can use different DELETE syntax.

Related SQL Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: