Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
communitycreator

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

Onyejiaku Theophilus Chidalu

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
Implementing the "WHERE" clause alongside the "DELETE" statement

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.

RELATED TAGS

sql
communitycreator

CONTRIBUTOR

Onyejiaku Theophilus Chidalu
RELATED COURSES

View all Courses

Keep Exploring