MySQL DELETE Statement

You can use theDELETE FROMcommand to delete records from MySQL data tables.

You can execute this command in themysql>command prompt or a PHP script.

Syntax

The following is the general syntax for the DELETE statement to delete data from MySQL data tables:

DELETE FROM table_name
WHERE condition;

Parameter description:

• table_nameis the name of the table from which you want to delete data.
• WHERE conditionis an optional clause used to specify the rows to delete. If you omit theWHEREclause, all rows in the table will be deleted.

More notes:

• If no WHERE clause is specified, all records in the MySQL table will be deleted.
• You can specify any condition in the WHERE clause.
• You can delete records from a single table in one operation.

The WHERE clause is very useful when you want to delete specific records from a data table.

Examples

The following examples demonstrate how to use the DELETE statement.

1. Delete rows that meet the conditions:

DELETE FROM students
WHERE graduation_year = 2021;

The above SQL statement deletes all records of students whose graduation_year is 2021 in the students table.

2. Delete all rows:

DELETE FROM orders;

The above SQL statement deletes all records from the orders table, but the table structure remains unchanged.

3. Use a subquery to delete rows that meet the conditions:

DELETE FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_date < '2023-01-01'
);

The above SQL statement uses a subquery to delete the customers corresponding to orders placed before '2023-01-01' in the orders table.

Note:When using the DELETE statement, make sure you provide sufficient conditions to ensure that only the rows you want to delete are deleted. If you do not provide a WHERE clause, all rows in the table will be deleted, which may lead to unpredictable results.

---

Deleting Data from the Command Line

Here we will use the WHERE clause in the DELETE command to delete selected data from the MySQL data table example_tbl.

Example

The following example deletes the record with example_id of 3 in the example_tbl table:

---

Deleting Data Using a PHP Script

PHP uses the mysqli_query() function to execute SQL statements. You can use the DELETE command with or without the WHERE clause.

This function has the samemysql>effect as executing SQL commands in the command prompt.

Example

The following PHP example will delete the record with example_id of 3 from the example_tbl table:

Other Extensions