Deleting Data by Using SQL DELETE Statement

To remove a data row or all rows from a 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 from of DELETE statement, followed the DELETE FROM is the table name you want to delete data. WHERE clause specifies which rows to remove. If the WHERE clause is ignored in the DELETE statement, all rows from table is removed.

The second form of DELETE statement, MySQL allows us 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 some example of using SQL DELETE statement with the sample database. It is recommended that you make a copy of employee table before practice with 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 ignore the WHERE clause:

DELETE FROM employees

It will remove all rows from employees table.

If you want to remove all employees, in employees table, who work in office with the officeCode is 1 and also the 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

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