Delete Records from a Table using SQL

Here are two ways 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

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:

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, to delete all the records where the price is greater than 500, add the condition 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, 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:

(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.

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.