Delete statement in MS SQL Server

A database contains many tables that have data stored in order. To delete the rows, the user needs to use a delete statement.

1. To DELETE a single record :

Syntax –

DELETE FROM table_name 
WHERE condition; 

Note –
Take care when deleting records from a table. Note that the WHERE clause in the DELETE statement. This WHERE specifies which record(s) need to be deleted. If you exclude the WHERE clause, all records in the table would be deleted.

Example –
A table named Student has multiple values inserted into it and we need to delete some value.

Table – Student

StudentName	RollNo	City
ABC	1	Jaipur
DEF	2	Delhi
JKL	3	Noida
XYZ	4	Delhi

The following SQL statement deletes a row from “Student” table which has StudentName as ‘ABC’.

DELETE FROM student 
WHERE StudentName = 'ABC';

Output –

(1 row(s) affected)

To check whether the value is actually deleted, the query is as follows :

select * 
from student;

Output –

StudentName	RollNo	City
DEF	2	Delhi
JKL	3	Noida
XYZ	4	Delhi

2. To DELETE all the records :
It is possible to delete all rows from a table without deleting the table. This means that the table structure, attributes, and indexes are going to be intact.

Syntax –

DELETE FROM table_name;

Example –
The following SQL statement deletes all rows from “Student” table, without deleting the table.

DELETE FROM student;

Output –

(3 row(s) affected)

To check whether the value is actually deleted, the query is as follows :

select * 
from student;

StudentName	RollNo	City

My Personal Notes arrow_drop_up

Save