How to Delete a Row in SQL
Database:
Operators:
Table of Contents
Problem
You want to remove a row / rows from a table.
Example 1
In the exam table, there are names of the students and the results of the exam.
| name | result |
|---|---|
| Janet Morgen | 9 |
| Taya Bain | 11 |
| Anne Johnson | 11 |
| Josh Kaur | 10 |
| Ellen Thornton | 8 |
You want to remove the row for Ellen Thornton.
Solution 1
DELETE FROM examWHERE name = 'Ellen Thornton'; |
The table exam now looks like this:
| name | result |
|---|---|
| Janet Morgen | 9 |
| Taya Bain | 11 |
| Anne Johnson | 11 |
| Josh Kaur | 10 |
Discussion
Use DELETE FROM with the name of the table from which you'd like to delete a row. In WHERE, write the condition specifying the row. If you have a specific row in mind, it is best to write the condition using the column containing unique values. Here, the unique column is name.
If there are many rows whose name equals 'Ellen Thornton', all of them will be removed. If there is no such name, no rows are removed.
Example 2
In the exam table, there are names of the students and the results of the exam, just as in the previous example.
| name | result |
|---|---|
| Janet Morgen | 9 |
| Taya Bain | 11 |
| Anne Johnson | 11 |
| Josh Kaur | 10 |
| Ellen Thornton | 8 |
You want to remove all rows for which the result is greater than 10.
Solution 2
DELETE FROM examWHERE result > 10; |
The exam table now looks like this:
| name | result |
|---|---|
| Janet Morgen | 9 |
| Josh Kaur | 10 |
| Ellen Thornton | 8 |
Discussion
Sometimes, you don't know the exact rows you need to remove or even how many rows you need to remove. You can write the condition for which the rows should be removed. There can be many rows that fulfill the condition, it can be just one row, or no rows at all.
Of course, you can write multiple conditions in WHERE, joining them with AND or OR, for example:
DELETE FROM examWHERE result > 10 AND name LIKE 'J%'; |