SQL – DELETE Statement

December 28, 2022

In the DELETE statement in SQL, it deletes the existing records from the table. It is operated using a condition in the WHERE clause. The condition in the where clause determines which records are going to be deleted. The WHERE clause is necessary because if the clause gets omitted, then all the records from the table get deleted as the WHERE clause determines which record needs to be deleted.

Syntax of DELETE statement –

DELETE FROM table_name WHERE condition ;

Demo Table –

Sales Order Table

Order date	Region	Rep	Item	Units	Unit Cost	Total
2021-01-23 00:00:00.000	Central	Kivell	Binder	50	19.99	999.5
2021-02-26 00:00:00.000	Central	Gill	Pen	27	19.99	539.73
2021-04-01 00:00:00.000	East	Jones	Binder	60	4.99	299.4
2021-06-08 00:00:00.000	East	Jones	Binder	60	8.99	539.4
2021-07-12 00:00:00.000	East	Howard	Binder	29	1.99	57.71
2021-07-29 00:00:00.000	East	Parent	Binder	81	19.99	1619.19
2021-09-01 00:00:00.000	Central	Smith	Desk	2	125	250
2021-09-18 00:00:00.000	East	Jones	Pen Set	16	15.99	255.84
2021-10-05 00:00:00.000	Central	Morgan	Binder	28	8.99	251.72
2021-10-22 00:00:00.000	East	Jones	Pen	64	8.99	575.36

Example – Consider the Sales Order table of the Customer database where we want to DELETE those records that have an order item as a pencil.

DELETE FROM [dbo].[SalesOrders] WHERE Item = 'Pen';

This output is the small section of the output by executing the above query –

Order date	Region	Rep	Item	Units	Unit_cost	Total
2021-01-23 00:00:00.000	Central	Kivell	Binder	50	19.99	999.5
2021-04-01 00:00:00.000	East	Jones	Binder	60	4.99	299.4
2021-06-08 00:00:00.000	East	Jones	Binder	60	8.99	539.4
2021-07-12 00:00:00.000	East	Howard	Binder	29	1.99	57.71
2021-07-29 00:00:00.000	East	Parent	Binder	81	19.99	1619.19
2021-09-01 00:00:00.000	Central	Smith	Desk	2	125	250
2021-09-18 00:00:00.000	East	Jones	Pen Set	16	15.99	255.84
2021-10-05 00:00:00.000	Central	Morgan	Binder	28	8.99	251.72
2021-11-25 00:00:00.000	Central	Kivell	Pen Set	96	4.99	479.04
2021-12-29 00:00:00.000	East	Parent	Pen Set	74	15.99	1183.26

As we can see in the results, the records that have “Pen” as their item got deleted, But the whole record didn’t get deleted as we had a condition in the WHERE clause.