MS SQL Server: DELETE Command in SQL Server

The DELETE statement in SQL Server (Transact-SQL) helps delete one or more records from a table in SQL Server.

DELETE command syntax in SQL Server

Simple syntax of DELETE command is as follows.

 DELETE FRO M bang 
 [WHERE dieu_kien]; 

The full syntax of the DELETE command is as follows.

 DELETE [TOP (g iatri_dau) [PERCENT] ] 
 FROM bang 
 [WHERE dieu_kien ]; 

Variable name or variable value

state

Table needs to delete the record.

WHERE dieu_kien

Option. Conditions that the record must meet to be deleted.

TOP (giatri_dau)

Option. If specifically, it will insert the first value of the row based on giatri_dau. For instance,, TOP (10) will insert the first 10 rows from the result set.

PERCENT

Option. If specified, the first rows are based on the percentage of giatri_dau of the result set. For instance,, TOP (10) PERCENT will insert 10% of the first value in the result set.

Note

There is no need to list fields in the DELETE statement because you will delete all rows in the table.

For instance, - use 1 condition

 DELETE FROM nh anvien 
 WHERE ten = 'Sarah'; 

This command will delete all records in the table with the employee name Sarah.

If you want to check the number of deleted items, run the SELECT command before executing the delete command.

 SELECT has un (*) 
 FROM nhanvien 
 WHERE ten = 'Sar ah'; 

For instance, - use 2 conditions

 DELETE FROM  nhanvien 
 WHERE ho = 'Johnson' 
 AND nhanvien_id > = 80; 

This command will delete all records in the table if the staff name is Johnson and nhanvien_id is greater than or equal to 80.

To know the number of rows deleted, run the SELECT statement below before running the DELETE command.

 SELECT coun t (*) 
 FROM nhanvien 
 WHERE ho = 'Johnson' 
 AND nhanvien_id > = 80; 

For instance, - use the keyword TOP

 DELETE  TOP(3) 
 FROM nhanvien 
 WHERE ho = 'Johnson'; 

This will delete the first 3 records in the table when the employee's family name is Johnson. If there are other records in this table that have a family name of Johnson, then they are not affected by this DELETE.

For instance, - use EXISTS clause

You can perform a more complex delete command, such as deleting records in a table based on values in another table, For instance,. Because it is impossible to render more than one table in the FROM clause in the DELETE statement, the EXISTS clause can be used as follows.

 DELETE FROM nhanvien 
 WHERE EXISTS 
 (SELECT * 
 FROM danhba 
 WHERE danhba.danhba_id = nhanvien.nhanvien_id 
 AND danhba.danhba_id <100); 

This DELETE statement will delete all records in the table when there is a record in the list of names that listener_id is less than 100 and list_id matches nhanvien_id.

Lesson: TRUNCATE TABLE command in SQL Server