How to use "delete" in SQL

Given a database table, we can delete records within the table or erase the entire table itself. Hence, when using the delete query in SQL one must be very careful.

Syntax

Let’s look at both implementations of the delete statement.

  1. The first implementation uses the WHERE clause;
DELETE FROM table_name
WHERE condition_statement;

Using the conditon_statement one can delete single or multiple records from within the table, depending on the requirement. The condition itself can contain various conditions.

  1. The second implementation is one in which the WHERE condition is not implemented, hence using this implementation one will delete the entire table.
DELETE FROM table_name;

Examples

Now that we know how to use the DELETE statement, let’s use it in practical examples for a better understanding.

1. Using a single condition

CREATE TABLE pets(
  pet_name varchar(20),
  age int,
  pet_type varchar(20)
);
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Doggo", 1, "Dog");
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Kittiness", 2, "Cat");
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Pupper", 3, "Dog");
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Parry", 10, "Bird");
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Bunny", 2, "Rabbit");
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Mousey", 5, "Rodent");
/*Printing the table*/
SELECT *
FROM pets;
/*Deleting where type is Dog*/
DELETE FROM pets
WHERE pet_type = "Dog";
/*Printing the table*/
SELECT "";
SELECT *
FROM pets;

2. Using multiple conditions

CREATE TABLE pets(
  pet_name varchar(20),
  age int,
  pet_type varchar(20)
);
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Doggo", 1, "Dog");
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Kittiness", 2, "Cat");
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Pupper", 3, "Dog");
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Parry", 10, "Bird");
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Bunny", 2, "Rabbit");
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Mousey", 5, "Rodent");
/*Printing the table*/
SELECT *
FROM pets;
/*Deleting where type is Bird or age is less than 3*/
DELETE FROM pets
WHERE pet_type = "Bird" OR age < 3;
/*Printing the table*/
SELECT "";
SELECT  *
FROM pets;

2. No WHERE clause is used

CREATE TABLE pets(
  pet_name varchar(20),
  age int,
  pet_type varchar(20)
);
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Doggo", 1, "Dog");
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Kittiness", 2, "Cat");
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Pupper", 3, "Dog");
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Parry", 10, "Bird");
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Bunny", 2, "Rabbit");
INSERT INTO pets(pet_name,age,pet_type)
VALUES("Mousey", 5, "Rodent");
/*Printing the table*/
SELECT *
FROM pets;
SELECT " ";
/*Deleting the entire table*/
DELETE FROM pets;
/*Printing the table*/
SELECT *
FROM pets;
Copyright ©2024 Educative, Inc. All rights reserved

Learn in-demand tech skills in half the time

PRODUCTS

Mock Interview

New

Courses

Skill Paths

Projects

Assessments

TRENDING TOPICS

Learn to Code

Tech Interview Prep

Generative AI

Data Science

Machine Learning

GitHub Students Scholarship

Early Access Courses

Pricing

For Individuals

Try for Free

Gift a Subscription

CONTRIBUTE

Become an Author

Become an Affiliate

Earn Referral Credits

RESOURCES

Blog

Cheatsheets

Webinars

Answers

ABOUT US

Our Team

Careers

Hiring

Frequently Asked Questions

Contact Us

Press

LEGAL

Privacy Policy

Cookie Policy

Terms of Service

Business Terms of Service

Data Processing Agreement

Copyright ©2024 Educative, Inc. All rights reserved.

soc2