SQL DELETE Statement

The DELETE statement is used to delete records in a table.

The DELETE Statement

The DELETE statement is used to delete rows in a table.

SQL DELETE Syntax

DELETE FROM table_name
WHERE some_column=some_value

Note: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

SQL DELETE Example

The "student" table:

Student_No	Student_Name	Address	Year_Study	Sex	Date_of_birth	Course_No
ST100	K.Ranjan	Kandy	1	Male	1988-01-17	C1
ST101	N.Kumanan	Jaffna	2	Male	1989-12-15	C2
ST102	S.Ranjini	Vavuniya	1	Female	1989-01-17	C1
ST103	K.Dias	Kalutura	3	Male	1987-07-22	C1
ST104	A.Kavitha	Matara	3	Female	1985-03-20	C2
ST105	L.Lavanya	Galle	2	Female	1989-04-22	C2
ST106	G.Moorthy	Jaffna	2	Male	1986-01-12	C1
ST107	F.Nathan	Matara	2	Male	1990-01-29	C3
ST108	A.H.M.Akmal	Puttalam	1	Male	1990-12-12	C3
ST109	K.Ravi	Kandy	1	Male	1988-02-17	C1
ST110	K.Mala	NULL	1	NULL	NULL	NULL

Now we want to delete the Student_Name"L.Lavanya " in the "student" table.

We use the following SQL statement:

DELETE FROM student
WHERE Student_Name='L.Lavanya';

The "Student" table will now look like this:

Student_No	Student_Name	Address	Year_Study	Sex	Date_of_birth	Course_No
ST100	K.Ranjan	Kandy	1	Male	1988-01-17	C1
ST101	N.Kumanan	Jaffna	2	Male	1989-12-15	C2
ST102	S.Ranjini	Vavuniya	1	Female	1989-01-17	C1
ST103	K.Dias	Kalutura	3	Male	1987-07-22	C1
ST104	A.Kavitha	Matara	3	Female	1985-03-20	C2
ST106	G.Moorthy	Jaffna	2	Male	1986-01-12	C1
ST107	F.Nathan	Matara	2	Male	1990-01-29	C3
ST108	A.H.M.Akmal	Puttalam	1	Male	1990-12-12	C3
ST109	K.Ravi	Kandy	1	Male	1988-02-17	C1
ST110	K.Mala	NULL	1	NULL	NULL	NULL

Delete All Rows

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

DELETE FROM table_name
       or
DELETE * FROM table_name

Note: Be very careful when deleting records. You cannot undo this statement!