Delete Records from a Table using SQL

February 12, 2022

Here are two approaches to delete records from a table using SQL:

(1) Delete records based on specified conditions:

DELETE FROM table_name
WHERE condition

(2) Delete all the records in a given table:

DELETE FROM table_name

In the next section, you’ll see how to apply the above approaches using a practical example.

The Example

Suppose that you created a table, where:

  • The table name is: product
  • The table has 3 columns: product_id, product_name and price

The ‘product’ table also contains the following 6 records:

product_id product_name price
1 Computer 800
2 TV 1200
3 Printer 150
4 Desk 400
5 Chair 120
6 Tablet 300

Here is the query to create the product table:

CREATE TABLE product (
	product_id int primary key,
	product_name nvarchar(50),
	price int
)

And here is the query to insert the 6 records into the table:

INSERT INTO product (product_id, product_name, price)

VALUES

(1,'Computer',800),
(2,'TV',1200),
(3,'Printer',150),
(4,'Desk',400),
(5,'Chair',120),
(6,'Tablet',300)

The ultimate goal is to delete the records from the above table.

(1) Delete Records Based on Specified Conditions

You can use the following template in order to delete the records based on specified conditions:

DELETE FROM table_name
WHERE condition

For example, let’s delete all the records where the price is greater than 500. In that case, you’ll need to add the condition of WHERE price > 500 to the query:

DELETE FROM product
WHERE price > 500

After running the above query, the first 2 records (where the price is greater than 500) will be deleted from the table.

To verify that the records were deleted, let’s run the following SELECT query:

SELECT * FROM product

You’ll notice that the 2 records, where the price is greater than 500, were indeed deleted:

product_id product_name price
3 Printer 150
4 Desk 400
5 Chair 120
6 Tablet 300

(2) Delete All the Records from a Table using SQL

What if you want to delete all the records from the table?

In that case, you can use the following template to delete all the records:

DELETE FROM table_name

For our example:

DELETE FROM product

After running the above query, all the records in the ‘product’ table will be deleted.

Let’s rerun the SELECT query:

SELECT * FROM product

You’ll then observe that all the records under the ‘product’ table were indeed deleted.

You may also want to check the following page for additional SQL tutorials.