Summary: In this tutorial, you will learn how to use the MySQL DELETE statement to delete data from a single table.
the MySQL DELETE statement
To delete data from a table, use the MySQL DELETE statement. The following is the syntax for the DELETE:DELETE
FROM table_name WHERE condition; Code language: SQL (Structured Query Language) (sql)In
- First, specify the table from which the data is deleted
- Second, use a condition to specify which rows to delete in the WHERE clause. The DELETE statement will delete rows that match the condition, note that the
WHERE clause is optional. If you omit the WHERE clause, the DELETE statement deletes all rows from the table.
In addition to deleting data from a table, the DELETE statement returns the number of deleted rows.
To delete data from multiple tables by using a single DELETE statement, use the DELETE JOIN statement that will be discussed in the next walkthrough.
To delete all rows from a table without knowing how many rows have been deleted, you must use the TRUNCATE TABLE statement for best 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 automatically deleted using the ON DELETE CASCADE option
. MySQL DELETE
Examples We will use the employees table in the sample database for the demonstration.
Please note that once you delete the data, it disappears. Later, you will learn how to place the DELETE statement on a transaction so that you can roll it back.
Suppose you want to delete employees whose officeNumber is 4, use the DELETE statement with the WHERE clause as shown in the following query:
DELETE FROM employees WHERE officeCode = 4; Code language: SQL (Structured Query Language) (sql)
To delete all rows from the employees table, use the DELETE statement without the WHERE clause as follows:DELETE FROM employees
; Code language: SQL (Structured Query Language) (sql)All rows in the
deleted employee table.
MySQL DELETE and LIMIT clause
If you want to limit the number of rows you want to delete, use the LIMIT clause as follows:
FROM table_table LIMIT row_count; Code language: SQL (Structured Query Language) (sql)
Note that the order of rows in a table is not specified, so when using the LIMIT clause, you should always use the ORDER BY clause.
DELETE FROM table_name ORDER BY C1, C2, … LIMIT row_count; Code language: SQL (Structured Query Language) (sql)Consider the following table of clients in the sample database:
For example, the following statement sorts clients by customer names alphabetically and deletes the first
10 clients:DELETE FROM
ORDER BY customerName LIMIT 10; Code language: SQL (Structured Query Language) (sql)Similarly, the following DELETE
statement selects customers in France, sorts them by credit limit from lowest to highest and deletes the first 5 customers:DELETE
FROM clients WHERE country = ‘France’ ORDER BY creditLimit LIMIT 5; Code language: SQL (Structured Query Language) (sql)
In this tutorial, you learned how to use the MySQL DELETE statement to delete data from a table.