Back to cookbooks list Articles Cookbook

How to Delete a Row in SQL

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.

nameresult
Janet Morgen9
Taya Bain11
Anne Johnson11
Josh Kaur10
Ellen Thornton8

You want to remove the row for Ellen Thornton.

Solution 1:

DELETE FROM exam
WHERE name = 'Ellen Thornton';

The table exam now looks like this:

nameresult
Janet Morgen9
Taya Bain11
Anne Johnson11
Josh Kaur10

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.

nameresult
Janet Morgen9
Taya Bain11
Anne Johnson11
Josh Kaur10
Ellen Thornton8

You want to remove all rows for which the result is greater than 10.

Solution 2:

DELETE FROM exam
WHERE result > 10;

The exam table now looks like this:

nameresult
Janet Morgen9
Josh Kaur10
Ellen Thornton8

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 exam
WHERE result > 10
  AND name LIKE 'J%';

Recommended courses:

Recommended articles:

See also: