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!

No comments:

Post a Comment