SQL Delete
SQL Delete
This page covers SQL Delete statement.
Previously, we saw how to add and modify records. This tutorial explores how to delete row data.
The syntax is easy. The keywords are “delete from” (with the addition of the “where” statement).
The SQL “where” statement is explored in further tutorials. Simply, its functionality is to filter data (select specific data).
This tutorial encompasses:
- delete all records
- delete specific records
- delete multiple records
Let’s use the table “student_info” from the previous tutorial.
| id | firstName | lastName | age |
|---|---|---|---|
| 1 | John | Smith | 21 |
| 2 | Patrick | Casey | 26 |
| 3 | Sam | Adams | 19 |
| 4 | Mike | Garcia | NULL |
Delete all records
The importance of the “where” statement can be seen here.
If we do not specify what to delete, all records will erase. Let’s see what happens without the “where” statement.
Please note the table structure stays intact.
The following is an example.
DELETE FROM student_info;
| id | firstName | lastName | age |
|---|---|---|---|
Delete specific records
The function of delete is very straightforward.
Choose from which table to delete as well as which specific data. This happens through the filtering statement “where“.
The syntax is very similar to the “update” statement. The keywords are “delete from where”.
The following is an example of deleting a specific row.
DELETE FROM student_info WHERE id=3;
1 row affected. Let’s see the outcome.
| id | firstName | lastName | age |
|---|---|---|---|
| 1 | John | Smith | 21 |
| 2 | Patrick | Casey | 26 |
| 4 | Mike | Garcia | NULL |
The third student info is missing.
Please note that the id does not change, and now it is 1, 2, and 4. When we insert another record, the generator continues with the number 5.
Delete multiple records
Similarly, the “delete” statement allows the removal of multiple data rows.
If we specify in the “where” statement a value that exists more than once in a column, all records will erase.
For this scenario, we use the original table and add a 5th student (Patrick Dumont, 21). This creates partial duplicates (two students with the same name, Patrick).
| id | firstName | lastName | age |
|---|---|---|---|
| 1 | John | Smith | 21 |
| 2 | Patrick | Casey | 26 |
| 3 | Sam | Adams | 19 |
| 4 | Mike | Garcia | NULL |
| 5 | Patrick | Dumont | 21 |
Now let’s delete all records containing the first name Patrick.
DELETE FROM student_info WHERE firstName='Patrick';
2 rows affected. Let’s check the result.
| id | firstName | lastName | age |
|---|---|---|---|
| 1 | John | Smith | 21 |
| 3 | Sam | Adams | 19 |
| 4 | Mike | Garcia | NULL |
Now second and fifth student data are deleted. This is due to both having the same first name Patrick.
Next: SQL Select
