Open In App

Delete statement in MS SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

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


Last Updated : 06 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads