DELETE Query

DELETE query is used to delete records form table.

DELETE FROM table [WHERE condition];

If you don’t specify any condition, then all the rows in the table are deleted.

mysql> SELECT * FROM employee;
+----+---------+------+-------------+---------------------+------------+
| id | name    | age  | salary      | mailId              | city       |
+----+---------+------+-------------+---------------------+------------+
|  1 | Hari    |   23 |  13580.2370 | hari@hari.com       | Bangalore  |
|  2 | Sandesh |   31 | 108179.5000 | sandesh@sandesh.com | Trivendram |
|  3 | Phalgum |   33 | 131280.2370 | phalgun@hari.com    | Hyderabad  |
|  4 | Manju   |   36 |  96433.5570 | manju@sandesh.com   | Bangalore  |
|  5 | Rakesh  |   26 |  41800.0000 | rakesh@hari.com     | Bangalore  |
|  6 | Sankalp |   38 |  96410.2370 | sankalp@sankalp.com | Chenai     |
|  7 | Vadiraj |   40 |  13580.2370 | vadi@hari.com       | Bangalore  |
|  8 | Prasob  |   37 |  13580.2370 | prasob@sandesh.com  | Trivendram |
|  9 | Kesav   | NULL | 135803.6790 | NULL                | NULL       |
+----+---------+------+-------------+---------------------+------------+
9 rows in set (0.00 sec)

a. Delete records from employee table where id = 7.

mysql> DELETE FROM employee WHERE id=7;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM employee;
+----+---------+------+-------------+---------------------+------------+
| id | name    | age  | salary      | mailId              | city       |
+----+---------+------+-------------+---------------------+------------+
|  1 | Hari    |   23 |  13580.2370 | hari@hari.com       | Bangalore  |
|  2 | Sandesh |   31 | 108179.5000 | sandesh@sandesh.com | Trivendram |
|  3 | Phalgum |   33 | 131280.2370 | phalgun@hari.com    | Hyderabad  |
|  4 | Manju   |   36 |  96433.5570 | manju@sandesh.com   | Bangalore  |
|  5 | Rakesh  |   26 |  41800.0000 | rakesh@hari.com     | Bangalore  |
|  6 | Sankalp |   38 |  96410.2370 | sankalp@sankalp.com | Chenai     |
|  8 | Prasob  |   37 |  13580.2370 | prasob@sandesh.com  | Trivendram |
|  9 | Kesav   | NULL | 135803.6790 | NULL                | NULL       |
+----+---------+------+-------------+---------------------+------------+
8 rows in set (0.00 sec)

b. Delete all employees whose salary is > 100000.

mysql> DELETE FROM employee WHERE salary > 100000;
Query OK, 3 rows affected (0.00 sec)  

mysql> SELECT * FROM employee;
+----+---------+------+------------+---------------------+------------+
| id | name    | age  | salary     | mailId              | city       |
+----+---------+------+------------+---------------------+------------+
|  1 | Hari    |   23 | 13580.2370 | hari@hari.com       | Bangalore  |
|  4 | Manju   |   36 | 96433.5570 | manju@sandesh.com   | Bangalore  |
|  5 | Rakesh  |   26 | 41800.0000 | rakesh@hari.com     | Bangalore  |
|  6 | Sankalp |   38 | 96410.2370 | sankalp@sankalp.com | Chenai     |
|  8 | Prasob  |   37 | 13580.2370 | prasob@sandesh.com  | Trivendram |
+----+---------+------+------------+---------------------+------------+
5 rows in set (0.00 sec)

 

Previous                                                 Next                                                 Home