MySQL DELETE – How to delete rows from a table?

By /
MySQL Delete Statement

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.

The MySQL 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.

Students Table Delete
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,

Delete Single Row

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 WHERE Clause.

DELETE FROM Students;Code language: SQL (Structured Query Language) (sql)

And after using the SELECT statement, you get the output as,

Delete All Rows

As you can see, we get the output as an Empty set, meaning there are no rows in the table.

Conclusion

The MySQL 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.

References