SQL DELETE FROM - statement to delete rows from table

<<Previous - SQL Create Table

Next - SQL Truncate >>

SQL DELETE FROM query is used to remove a record from table. DELETE FROM clause can only delete the data from the table and not the table structure itself.

Syntax

DELETE FROM Table-name
WHERE condition;

Example

Below consider the demo table 'student' from 'Collegedb'database.

mysql> SELECT * FROM student;
+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      1 | Aruna   |   18 | Chennai   |
|      2 | Varun   |   19 | Bangalore |
|      3 | Ara     |   19 | Kerala    |
|      4 | Markdin |   18 | Mumbai    |
|      5 | Kannan  |   20 | Kerala    |
|      6 | Kanika  |   18 | Chennai   |
|      7 | Jose    |   19 | Kerala    |
+--------+---------+------+-----------+
7 rows in set (1.39 sec)

following query delete the list of records from student table.

mysql> DELETE FROM student WHERE Age =18;
Query OK, 3 rows affected (0.02 sec)

mysql> SELECT * FROM student;
+--------+--------+------+-----------+
| RollNo | Name   | Age  | City      |
+--------+--------+------+-----------+
|      2 | Varun  |   19 | Bangalore |
|      3 | Ara    |   19 | Kerala    |
|      5 | Kannan |   20 | Kerala    |
|      7 | Jose   |   19 | Kerala    |
+--------+--------+------+-----------+
4 rows in set (0.00 sec)

If you omit WHERE clause in the DELETE statement, it will remove all records from the table.

Following query removes all rows from the table.

mysql> SELECT * FROM student;
+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      1 | Aruna   |   18 | Chennai   |
|      2 | Varun   |   19 | Bangalore |
|      3 | Ara     |   19 | Kerala    |
|      4 | Markdin |   18 | Mumbai    |
|      5 | Kannan  |   20 | Kerala    |
|      6 | Kanika  |   18 | Chennai   |
|      7 | Jose    |   19 | Kerala    |
+--------+---------+------+-----------+
7 rows in set (0.00 sec)

mysql> DELETE FROM student ;
Query OK, 7 rows affected (0.01 sec)

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

<< Previous - SQL Create Table

Next - SQL Truncate >>