Blogs

MySQL DELETE – Deleting Data from a Table

Posted on by admin

Summary: In this tutorial, you will learn how to use the MySQL DELETE statement to delete data from a single table.

Introduction to

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

this statement:

  • 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.

MySQL DELETE - Employees Table

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:

DELETE

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 clients 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 clients: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.

admin