Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

delete
remove
sql
database

How to use "delete" in SQL

Educative Answers Team

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;

RELATED TAGS

delete
remove
sql
database
Copyright ©2022 Educative, Inc. All rights reserved
RELATED COURSES

View all Courses

Keep Exploring