Deleting Data from Database Tables

To remove all rows or records from a database table you can use SQL DELETE statement. The syntax of SQL DELETE statement in MySQL is as follows:

 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]

In the first form of DELETE statement, followed the DELETE FROM part is the table name which you want to delete the data. WHERE clause specifies condition to limit which rows you want to to remove. If a record meet WHERE condition, it will be removed from the database table. If the WHERE clause is ignored in the DELETE statement, all rows of table are deleted.

The second form of DELETE statement, MySQL allows you to delete row(s) from multiple tables with references to other table.

The third form of DELETE statement is quite similar to the second form except that instead of FROM keyword it uses USING keyword.

Let's have a couples of examples of using SQL DELETE statement in the sample database. It is recommended that you make a copy of employee table before practicing with the DELETE statement.

If you want to delete all employees in an office with officeNumber is 4, just execute this query:

 DELETE FROM employees 
 WHERE officeCode = 4  

To delete all employees, just remove the WHERE clause as follows:

 DELETE FROM employees 

It will remove all rows from employees table.

If you want to remove all employees, in employees table, who work in the office with the officeCode 1 and also that office, just use the second form of DELETE statement:

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

You can of course achieve the same above result 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 learn various form of SQL DELETE FROM statement of MySQL to delete records from one or more database table.