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.

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:

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.

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:

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%';