Summary: The MySQL DELETE statement not only allows you to delete record from one table but also multiple tables. In this tutorial, you will learn how to use MySQL DELETE statement with examples.
To remove all rows or records from a database table you use MySQL DELETE statement. The following illustrates MySQL DELETE statement:
1 |
DELETE [LOW_PRIORITY] [QUICK] |
3 |
[WHERE conditions] [ORDER BY ...] [LIMIT rows] |
1 |
DELETE [LOW_PRIORITY] [QUICK] |
2 |
table_name[.*] [, table_name[.*] ...] |
4 |
[WHERE where_definition] |
1 |
DELETE [LOW_PRIORITY] [QUICK] |
2 |
FROM table_name[.*] [, table_name[.*] ...] |
4 |
[WHERE where_definition] |
Let’s examine the MySQL DELETE statements in details as below:
- In the first form of MySQL DELETE statement, followed the DELETE FROM part is the table name where you want to delete records. The WHERE clause specifies condition to limit which rows you want to to remove. If a record meets WHERE condition, it will be removed from the database table permanently. If the WHERE clause is ignored in the MySQL DELETE statement, all rows of the table are deleted.
- The second form of MySQL DELETE statement, It deletes records from multiple tables which reference to other table.
- The third form of MySQL DELETE statement is quite similar to the second one except Using keyword is used instead of FROM keyword.
Let’s have a couple of examples of using MySQL DELETE statement in the sample database.
It is recommended that you make a copy of employee table before practicing with the MySQL DELETE statement.
Suppose you want to delete all employees in an office with officeNumber is 4, just execute the following query:
To delete all employees from all offices, just remove the WHERE condition as follows:
It will remove all rows from employees table.
If you want to delete all employee who work for office with officecode 1 and also that office. You can use the second form of MySQL DELETE statement to delete data from multiple tables as follows:
1 |
DELETE employees,offices |
3 |
WHERE employees.officeCode = offices.officeCode AND |
You can achieve the same above effect by using the third form of DELETE statement as below:
1 |
DELETE FROM employees,offices |
2 |
USING employees,offices |
3 |
WHERE employees.officeCode = offices.officeCode AND |
In this tutorial, you’ve learned various forms of MySQL DELETE statement to delete records from one or multiple database tables.
Related posts:
- How to Use MySQL Limit to Constrain Number of Returned Records
- Working with Tables – Part II
- Inserting Data into Database Tables
- Updating Data in Database Tables
- Managing Database Index in MySQL
