MySQL DELETE Statement

Last Updated: 28 May, 2023

In MySQL, we can delete one or more records from the table using the DELETE statement.

Note: It is highly recommended that we always keep our database's latest backup because if records deleted by mistake or for any reasons we can recover the deleted records from the database backup.

Let's see the syntax and example of DELETE statements in MySQL.

Syntax:

 DELETE FROM table_name;

Example:

 DELETE FROM student;

After execution of the delete statement over the student table, all the records from the student table will be deleted.

Deleting conditional or specific records from a table:

We can also delete some sets of specific or conditional records from a table with the help of the WHERE clause. In the DELETE statement, the WHERE clause is optional. WHERE clause specifies which records we want to delete.

Let's see the syntax and example of DELETE statements with WHERE clause in MySQL.

Syntax:

 DELETE FROM table_name; WHERE condition;

Example:

 DELETE FROM student WHERE roll = 10;

Example: WHERE clause with AND statement

 DELETE FROM student WHERE roll = 10 AND class = “LKG”;

 DELETE FROM student WHERE roll = 10 AND class = “LKG” AND section = “B”;

Example: WHERE clause with OR statement

 DELETE FROM student WHERE roll = 10 OR class = “LKG”;

 DELETE FROM student WHERE roll = 10 OR class = “LKG” OR section = “B”;

Example: WHERE clause with AND and OR statements

 DELETE FROM student WHERE roll = 10 AND (class = “LKG” OR section = “B”);

Example: WHERE clause with NOT ( != or <> ) statement.

 DELETE FROM student WHERE roll != 10;

 DELETE FROM student WHERE roll <> 10;

 DELETE FROM student WHERE roll != 10 AND class = “LKG” AND section = “B”;

 DELETE FROM student WHERE roll != 10 AND (class = “LKG” OR section = “B”);

Delete records from multiple tables:

Prerequisite: MySQL JOIN Tutorial

We can delete records from a single table or multiple tables using the DELETE statement; we only need to add the JOIN clause with the DELETE statement.

 DELETE student address FROM student 
INNER JOIN address 
ON student.roll = address.roll
WHERE roll = 35;

In the above statement, we are deleting records from two tables, the student and address tables, based on the given condition that roll = 35.