LSP4YOU - Learner's Support Publications

SQL - DELETE Statement

In the database structured query language (SQL), the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.

The general syntax is:

DELETE FROM table_name [WHERE condition];

DELETE FROM table_name tells MySQL server to remove all the rows from the table, and [WHERE condition] is optional and is used to put a filter that restricts the number of rows affected by the DELETE query. If the WHERE clause is not used in the DELETE query, then all the rows in a given table will be deleted.

For Example:

mysql> select * from department;
+---------+----------------+------------------+
| dept_id | dept_name      | location         |
+---------+----------------+------------------+
| D01     | Computer       | South Block      |
| D02     | Commerce       | E K Block Wing-2 |
| D03     | Administration | E K Block Wing-1 |
| D04     | Psychology     | E K Block Wing-3 |
| D05     | Electronics    | NULL             |
| D06     | Biosciences    | Main Block       |
+---------+----------------+------------------+
6 rows in set (0.01 sec)

mysql> delete from department where dept_id = 'D06';
Query OK, 1 row affected (0.07 sec)

mysql> select * from department;
+---------+----------------+------------------+
| dept_id | dept_name      | location         |
+---------+----------------+------------------+
| D01     | Computer       | South Block      |
| D02     | Commerce       | E K Block Wing-2 |
| D03     | Administration | E K Block Wing-1 |
| D04     | Psychology     | E K Block Wing-3 |
| D05     | Electronics    | NULL             |
+---------+----------------+------------------+
5 rows in set (0.00 sec)

mysql>