In this tutorial, we will learn about the MySQL
DELETE query. Suppose you are a school teacher and you maintain a table of records of all the students in your class. Now, in the middle of the year, a student transfers to a different school. Keeping that student’s record would be a waste as it won’t be needed from now on. So the school teacher wants to delete that record. We use the MySQL
DELETE query just for that.
DELETE query is used to delete the existing records of a table. The
DELETE query is an example of the Data Manipulation Language (DML).
Syntax of MySQL DELETE
DELETE FROM table_name WHERE condition;Code language: SQL (Structured Query Language) (sql)
Important NOTE: The WHERE clause is optional. A MySQL DELETE query run without any the WHERE condition will delete all the rows of the table specified.
Examples of MySQL DELETE
Consider the following Students table.
1. Deleting A Single Row using MySQL DELETE
Suppose the student named Devika Kulkarni with ID 2 transferred to a different school and you want to remove her record from your table. We do this using the query,
DELETE FROM Students WHERE ID=2;Code language: SQL (Structured Query Language) (sql)
Notice how we’ve used the WHERE statement here. We then use the
SELECT Statement to get the output as,
As you can see, the record was deleted from the table. Note that, the usage of the
WHERE clause may result in multiple records getting deleted depending on the condition you put in.
2. Deleting All Rows
Suppose all students in your class have graduated and you need to delete all those records before entering the records of new students. So, how do you delete all records in a table? Well, you use the
DELETE statement without the
DELETE FROM Students;Code language: SQL (Structured Query Language) (sql)
And after using the
SELECT statement, you get the output as,
As you can see, we get the output as an Empty set, meaning there are no rows in the table.
DELETE Statement is an important DML statement that enables us to delete all rows in a table, without deleting the table itself. This is very advantageous when removing several rows from a large database table.
- MySQL documentation on the
- JournalDev article on MySQL