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.
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;
DELETE FROM student WHERE roll = 10 AND class = “LKG”;
DELETE FROM student WHERE roll = 10 AND class = “LKG” AND section = “B”;
DELETE FROM student WHERE roll = 10 OR class = “LKG”;
DELETE FROM student WHERE roll = 10 OR class = “LKG” OR section = “B”;
DELETE FROM student WHERE roll = 10 AND (class = “LKG” OR section = “B”);
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”);
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.
That's all, guys. I hope this MySQL article is helpful for you.
Happy Learning... 😀
feedback@javabytechie.com