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:

Customers

CustomerID

Name

Country

1

Theo David

U.S.A

2

Peter Mark

United Kingdom

3

Antonio Clark

Mexico

4

Benjamin Monday

U.S.A

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.

-- using the WHERE clause alongside the SELECT statement
DELETE FROM Customers
WHERE CustomerName='Benjamin Monday';
-- printing the modified table
SELECT * FROM Customers

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.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)