Introduction

DELETE statement in MySQL removes one or more rows in a table permanently. Use the WHERE clause to DELETE only specific records.

Syntax

DELETE FROM table_name
WHERE
    condition;

It will delete record from table table_name based on the condition passed to optional WHERE clause. To remove one or more rows in a table:

  • Specify the table name to remove data in the DELETE FROM clause.
  • Add a condition in the WHERE clause to specify which rows to remove. If you omit the WHERE clause, the statement will remove all rows in the table.

To limit the number of rows to delete, use the LIMIT clause as follows:

DELETE FROM table_name
LIMIT row_count;

It will delete first row_count rows from the table. Order of rows in above syntax is unspecified. To specify the row order

DELETE FROM table_name
ORDER BY column_1
LIMIT row_count;

Above statement sorts records in table_name based on column_1 and deletes the first row_count rows from the table.

Example

Consider below table

Employee Information Table
Employee Information Table

Below example delete the row having id equal to 4 from the above table. Last statement in the example, deletes all the rows from the table.

/* Delete the employee with id 4 */
DELETE FROM employleeinfo WHERE id=4

/* Remove all rows in the table*/
DELETE FROM employleeinfo;

Following DELETE statement sorts the employee by their first name, and deletes the first 5 rows.

DELETE FROM employleeinfo
ORDER BY firstName
LIMIT 5;