MySQL DELETE

Introduction to MySQL DELETE statement

To delete data from a table, you use the MySQL DELETE statement. The following illustrates the syntax of the DELETE statement:

DELETE FROM table_name
WHERE condition;

In this statement:

  • First, specify the table from which you delete data.
  • Second, use a condition to specify which rows to delete in the WHERE clause. The DELETE statement will delete rows that match the condition,

Notice that the WHERE clause is optional. If you omit the WHERE clause, the DELETE statement will delete all rows in the table.

Besides deleting data from a table, the DELETE statement returns the number of deleted rows.

To delete data from multiple tables using a single DELETE statement, you use the DELETE JOIN statement which will be covered in the next tutorial.

To delete all rows in a table without the need of knowing how many rows deleted, you should use the TRUNCATE TABLE statement to get better performance.

For a table that has a foreign key constraint, when you delete rows from the parent table, the rows in the child table will be deleted automatically by using the ON DELETE CASCADE option.

MySQL DELETE examples

We will use the employees table in the sample database for the demonstration.

Note that once you delete data, it is gone. Later, you will learn how to put the DELETE statement in a transaction so that you can roll it back.

Suppose you want to delete employees whose the officeNumber is 4, you use the DELETE statement with the WHERE clause as shown in the following query:

DELETE FROM employees 
WHERE officeCode = 4;

To delete all rows from the employees table, you use the DELETE statement without the WHERE clause as follows:

DELETE FROM employees;

All rows in the employees table deleted.

MySQL DELETE and LIMIT clause

If you want to limit the number of rows to delete, use the LIMIT clause as follows:

DELETE FROM table_table
LIMIT row_count;

Note that the order of rows in a table is unspecified, therefore, when you use the LIMIT clause, you should always use the ORDER BY clause.

DELETE FROM table_name
ORDER BY c1, c2, ...
LIMIT row_count;

Consider the following customers table in the sample database:

For example, the following statement sorts customers by customer names alphabetically and deletes the first 10 customers:

DELETE FROM customers
ORDER BY customerName
LIMIT 10;

Similarly, the following DELETE statement selects customers in France, sorts them by credit limit in from low to high, and deletes the first 5 customers:

DELETE FROM customers
WHERE country = 'France'
ORDER BY creditLimit
LIMIT 5;

 

  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Managing Hierarchical Data in MySQL Using the Adjacency List Model

Hierarchical data is everywhere. It can be blog categories, product hierarchies, or organizational structures. There are many ways to manage hierarchical data in MySQL and the adjacency list model...

MySQL Row Count: How to Get Row Count in MySQL

Getting MySQL row count of a single table To get the row count of a single table, you use the COUNT(*) in a SELECT statement as follows: SELECT COUNT(*) FROM table_name; For example,...

MySQL Insert

Introduction to the MySQL INSERT statement The INSERT statement allows you to insert one or more rows into a table. The following illustrates the syntax of the INSERT statement: INSERT INTO...

MySQL Compare Two Tables

In data migration, we often have to compare two tables to identify a record in one table that have no corresponding record in another table. For example, we have a new database whose schema is...

How To Find Duplicate Values in MySQL

Data duplication happens because of many reasons. Finding duplicate values is one of the important tasks that you must deal with when working with the databases. Setting up a sample table First,...