MySQL | DELETE
Start your free 7-days trial now!
MySQL's DELETE statement is used to delete existing records in a table.
You need the DELETE privilege on a table to delete records from it.
Syntax
DELETE FROM table_nameWHERE condition;
Examples
Consider the following table about whether students hold a drivers license:
student_id | full_name | age | has_license |
|---|---|---|---|
1 | Sky Towner | 17 | 0 |
2 | Ben Davis | 19 | 1 |
3 | Travis Apple | 18 | 0 |
4 | Arthur David | 16 | 0 |
5 | Benjamin Town | 17 | 1 |
The above sample table can be created using the code here.
Deleting a single record
To delete the record for 'Ben Davis' as he has now left the school:
DELETE FROM drivers_licenseWHERE student_id = 2;
Query OK, 1 row affected (0.01 sec)
We can see that 'Ben Davis' has now been removed from drivers_license table:
SELECT *FROM drivers_license;
+------------+---------------+------+-------------+| student_id | full_name | age | has_license |+------------+---------------+------+-------------+| 1 | Sky Towner | 18 | 0 || 3 | Travis Apple | 18 | 0 || 4 | Arthur David | 16 | 0 || 5 | Benjamin Town | 17 | 1 |+------------+---------------+------+-------------+
Take care whenever you are performing DELETE on your table. If we forget to add the WHERE clause, all the records in the table will be deleted.
DELETE FROM drivers_license;
SELECT *FROM drivers_license;
Empty set (0.01 sec)
Deleting multiple records
Consider the following table about whether students hold a drivers license:
student_id | full_name | age | has_license |
|---|---|---|---|
1 | Sky Towner | 17 | 0 |
2 | Ben Davis | 19 | 1 |
3 | Travis Apple | 18 | 0 |
4 | Arthur David | 16 | 0 |
5 | Benjamin Town | 17 | 1 |
To delete records for students with student_id 1, 2 or 3:
DELETE FROM drivers_licenseWHERE student_id IN (1, 2, 3);
SELECT *FROM drivers_license;
+------------+---------------+------+-------------+| student_id | full_name | age | has_license |+------------+---------------+------+-------------+| 4 | Arthur David | 16 | 0 || 5 | Benjamin Town | 17 | 1 |+------------+---------------+------+-------------+
Before using the DELETE statement, perform a SELECT statement with the same conditions to see what exactly you will be deleting. Moreover, whenever you are performing any large operation with your data, make sure to back it up first!
