How to use the where clause with the delete statement in SQL

Overview

In SQL, the WHERE clause is used alongside the DELETE statement to delete a specified record from a table. The WHERE clause is commonly used with comparison operators. These include:

1. = (equal to operator)
2. != (not equal to operator)
3. > (greater than operator)
4. < (less than operator)
5. >= (greater than or equal to operator)
6. <= (less than or equal to operator)

It is worth noting that:

• If the WHERE clause has the primary key with the = operator, then only one record can be affected —if they are found.
• Similarly, like in the case described above, if the WHERE clause has the non-primary key with the = operator, then multiple records can be affected —if they are found.
• Irrespective of the key type (either primary or non-primary) and with the inequality operators < or >, multiple records will be affected, if they are found.

Syntax

The syntax of the DELETE statement is given below:

DELETE FROM name_of_table
WHERE condition;

Database schema

The database we will be working with is shown below:

Code example

In the code written below, we will use the WHERE clause alongside the DELETE statement to delete a specific part of the Customers table.

Code explanation

• We declare the Customers table and populate it with values, according to the defined database schema on our platform.
• In the statements shown above, we apply the WHERE clause and the DELETE statement with a condition of where the CustomerName is Benjamin Monday.
• Then, we print the modified table called Customers.

Note: Notice from the output of the code that the record which has the CustomerName as Benjamin Monday has been deleted.