MySQL DELETE

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,

1 2 3 4

DELETE FROM     table_name WHERE     some_column=some_value;

Notice the WHERE clause in the MySQL DELETE statement!
The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

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:

1 2	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:

1	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:

1 2 3 4

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.

1 2	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.

---

MySQL UPDATE How to Manage Database

---