Summary: In this chapter, we will learn how to use the MySQL DELETE statement to remove data from table.

Introducing to MySQL DELETE

The DELETE statement is used to delete rows in a table. MySQL DELETE statement allows to remove records from not only one table but also multiple tables using a single DELETE statement.

We use the following DELETE statement to remove data from a single table,

DELETE FROM 
    table_name
WHERE 
    some_column=some_value;

The DELETE statement returns the number of rows deleted specified by the ROW_COUNT() function. The ROW_COUNT() function returns the number of rows inserted, updated or deleted by the last INSERT, UPDATE or DELETE statement

MySQL Delete statement examples

Ex-1: Suppose we want to remove employees whose branchid is 2, we can use the DELETE statement with the WHERE clause as the following query:

DELETE FROM employees
WHERE branchid= 2;

Ex-2: To delete all employee records from the employees table, we can use the DELETE statement without the WHERE clause as follows:

DELETE FROM employees

Ex-3: Suppose one branch is closed and we want to remove all employee records from employees table and branch also itself from the branches table.

The following query removes the branch record which branchid is 2 in the branches table and also removes all employee records associated with the branchid 2 from the employees table:

DELETE employees, branches
FROM employees, branches
WHERE employees.branchid = branches.branchid 
      AND branches.branchid = 2;

We can verify the changes by using the following SELECT statements to query data from both employees and branches table.

SELECT * FROM employees WHERE branchid = 2;
SELECT * FROM branches WHERE branchid = 2;

In this chapter, we have learnt various forms of the MySQL DELETE statement to delete records from one or more tables.