MySQL - DELETE FROM Table

In this tutorial we will learn to delete data from a table in MySQL.

We use the DELETE FROM table_name to delete data from tables.

DELETE Syntax

DELETE FROM table_name
WHERE condition;

Delete single row using primary key

In the following example we are deleting comment having commentid 10.

Before DELETE:

mysql> SELECT * FROM comments;
+-----------+------------+---------------------+---------------------+---------------------+
| commentid | employeeid | commentbody         | lastmodified        | created             |
+-----------+------------+---------------------+---------------------+---------------------+
|         1 | e03        | Awesome             | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         2 | e03        | Hello World         | 2018-01-02 01:02:03 | 2018-01-02 01:02:03 |
|         3 | e01        | Happy               | 2018-01-02 01:04:03 | 2018-01-02 01:04:03 |
|         4 | e04        | This is my comment. | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         5 | e01        | Keep coding....     | 2018-04-04 03:02:01 | 2018-05-04 03:02:01 |
|         6 | e04        | Hello               | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 |
|         7 | e01        | ABC...              | 2018-04-04 04:04:01 | 2018-05-04 04:02:01 |
|         8 | e05        | Andriod             | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 |
|         9 | e03        | Apple               | 2018-04-04 04:04:01 | 2018-05-04 04:02:01 |
|        10 | e01        | Super               | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 |
|        11 | e02        | Awesome             | 2018-04-04 04:04:01 | 2018-05-04 04:02:01 |
+-----------+------------+---------------------+---------------------+---------------------+
11 rows in set (0.00 sec)

DELETE Query:

mysql> DELETE FROM comments WHERE commentid = 10;
Query OK, 1 row affected (0.00 sec)

After DELETE:

mysql> SELECT * FROM comments;
+-----------+------------+---------------------+---------------------+---------------------+
| commentid | employeeid | commentbody         | lastmodified        | created             |
+-----------+------------+---------------------+---------------------+---------------------+
|         1 | e03        | Awesome             | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         2 | e03        | Hello World         | 2018-01-02 01:02:03 | 2018-01-02 01:02:03 |
|         3 | e01        | Happy               | 2018-01-02 01:04:03 | 2018-01-02 01:04:03 |
|         4 | e04        | This is my comment. | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         5 | e01        | Keep coding....     | 2018-04-04 03:02:01 | 2018-05-04 03:02:01 |
|         6 | e04        | Hello               | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 |
|         7 | e01        | ABC...              | 2018-04-04 04:04:01 | 2018-05-04 04:02:01 |
|         8 | e05        | Andriod             | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 |
|         9 | e03        | Apple               | 2018-04-04 04:04:01 | 2018-05-04 04:02:01 |
|        11 | e02        | Awesome             | 2018-04-04 04:04:01 | 2018-05-04 04:02:01 |
+-----------+------------+---------------------+---------------------+---------------------+
10 rows in set (0.00 sec)

Delete multiple rows using WHERE clause

In the following example we will delete all the comments by the employeeid 'e03'.

mysql> DELETE FROM comments WHERE employeeid = 'e03';
Query OK, 3 rows affected (0.00 sec)

After DELETE:

mysql> SELECT * FROM comments;
+-----------+------------+---------------------+---------------------+---------------------+
| commentid | employeeid | commentbody         | lastmodified        | created             |
+-----------+------------+---------------------+---------------------+---------------------+
|         3 | e01        | Happy               | 2018-01-02 01:04:03 | 2018-01-02 01:04:03 |
|         4 | e04        | This is my comment. | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         5 | e01        | Keep coding....     | 2018-04-04 03:02:01 | 2018-05-04 03:02:01 |
|         6 | e04        | Hello               | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 |
|         7 | e01        | ABC...              | 2018-04-04 04:04:01 | 2018-05-04 04:02:01 |
|         8 | e05        | Andriod             | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 |
|        11 | e02        | Awesome             | 2018-04-04 04:04:01 | 2018-05-04 04:02:01 |
+-----------+------------+---------------------+---------------------+---------------------+
7 rows in set (0.00 sec)

Delete without WHERE clause

In the following example we are running the DELETE query without any WHERE clause and so, it will result in the deletion of all the rows.

Do not run DELETE query without a WHERE condition. You will end up deleting something that you never wanted.

mysql> DELETE FROM comments;
Query OK, 7 rows affected (0.00 sec)

After DELETE:

mysql> SELECT * FROM comments;
Empty set (0.00 sec)