» Basic MySQL Tutorial » Deleting Records from Tables by Using MySQL Delete

Deleting Records from Tables Using MySQL DELETE

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:

 DELETE [LOW_PRIORITY] [QUICK]   
 FROM table_name   
[WHERE conditions] [ORDER BY ...] [LIMIT rows]

 DELETE [LOW_PRIORITY] [QUICK]  
  table_name[.*] [, table_name[.*] ...]   
 FROM table-references   
 [WHERE where_definition] 

  DELETE [LOW_PRIORITY] [QUICK] 
  FROM table_name[.*] [, table_name[.*] ...]   
  USING table-references   
  [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:

 DELETE FROM employees 
 WHERE officeCode = 4

To delete all employees from all offices, just remove the WHERE condition as follows:

 DELETE FROM employees

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:

 DELETE employees,offices 
 FROM employees,offices 
 WHERE employees.officeCode = offices.officeCode  AND 
       offices.officeCode = 1

You can achieve the same above effect by using the third form of DELETE statement as below:

 DELETE FROM employees,offices 
 USING employees,offices 
 WHERE employees.officeCode = offices.officeCode  AND 
       offices.officeCode = 1

In this tutorial, you've learned various forms of MySQL DELETE statement to delete records from one or multiple database tables.

Search