MySQL DELETE Statement

In this tutorial, we will learn abut MySQL DELETE statement. We can use the DELETE statement to remove rows from the database table.

Syntax

The DELETE statement uses the following syntax to delete all the rows in the table.

This will remove all the rows and empty the table. We don’t need to mention the columns since it will delete the entire rows from the table.

mysql> DELETE FROM <table_name>;

The DELETE statement allows a WHERE clause to identify which rows to delete. The WHERE clause identifies which rows to remove from the table.

mysql> DELETE FROM <table_name>
              WHERE <condition(s)>;

The DELETE statement will display the number of rows affected by the statement.

LIMIT clause

The DELETE statement supports other clauses like ORDER BY and LIMIT. These clauses offer finer control over the way the rows are deleted from the table.

mysql> DELETE FROM <table>
               WHERE <condition>
               LIMIT <delete_count>;

ORDER BY clause

In general, MySQL

mysql> DELETE FROM <table>
               WHERE <condition>

ORDER BY <column_name> <sort_order>
               LIMIT <delete_count>;

 

Example

In this example, we will delete a row from the City table from the world MySQL database. The table structure and the row is shown below:

mysql> DESC City;
+————-+———-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————-+———-+——+—–+———+—————-+
| ID | int | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int | NO | | 0 | |
+————-+———-+——+—–+———+—————-+
5 rows in set (0.00 sec)

mysql> SELECT * FROM City WHERE ID = 500;
+—–+——–+————-+———-+————+
| ID | Name | CountryCode | District | Population |
+—–+——–+————-+———-+————+
| 500 | Bolton | GBR | England | 139020 |
+—–+——–+————-+———-+————+
1 row in set (0.00 sec)

Delete the row using the DELETE statement.

mysql> DELETE FROM City WHERE ID=500;
Query OK, 1 row affected (0.02 sec)

 

Now query for the row in the table.

mysql> SELECT * FROM City WHERE ID = 500;
Empty set (0.01 sec)

 

MySQL Tutorials

MySQL Tutorials on this website:

https://www.testingdocs.com/mysql-tutorials-for-beginners/