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.

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 –

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;

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

khushboogoyal499

Check out this Author's contributed articles.