As the name suggests, the SQL DELETE statement is used to delete existing records from a database table when they are no longer needed. Using DELETE statement, you can delete single or multiple records from the database. The DELETE command is used more frequently with the WHERE clause when removing unnecessary records from the database.
SQL Server supports these ways to delete data in an existing table:
The DELETE statement.
The deletion of the current row in a result set or cursor.
The TRUNCATE TABLE statement.
For this exercise however we are going to focus on deleting data in SQL using the DELETE statement. The DELETE statement removes one or more rows in a table or view. A simplified form of the DELETE syntax is:
DELETE Syntaxtable_or_view is the name the table or view from which the rows are to be deleted. All rows in table_or_view that meet the qualifications of the WHERE search condition are deleted.
If a WHERE clause is not specified, all the rows in table_or_view are deleted.
The FROM clause specifies additional tables or views and join conditions that can be used by the predicates in the WHERE clause search condition to qualify the rows to be deleted from table_or_view.
Rows are not deleted from the tables named in the FROM clause, only from the table named in table_or_view.
Any table that has all rows removed remains in the database. The DELETE statement deletes only rows from the table, and the table must be removed from the database by using the DROP TABLE statement.
Below are a three scripts demonstrating the use of DELETE statements and showing a series of deletes of differing complexity. This script below shows the three DELETE statements needed to delete the rows associated with products supplied by the company named Lyngbysild in the Northwind database. This would not be a typical business operation because it involves deleting lines from existing orders, but it does show a series of deletes of differing complexity.
To delete limited records in the database, use the WHERE clause along with DELETE command. For example:
Another example:
USE Northwind
GO
DELETE FROM Employee WHERE Employee_Id IN(3,5);
GO
Once we execute the above query will remove the employees with employee IDs 3 & 5 entries from the employee table
//////////////////////////////////Warning!
Once you delete records using the DELETE command, those records cannot be recovered again, so make sure
you backup your data prior to executing DELETE command.
DECLARE abc CURSOR FOR
SELECT * FROM MyTable
OPEN abc
FETCH NEXT FROM abc
DELETE MyTable WHERE CURRENT OF abc
CLOSE abc
DEALLOCATE abc
Note
this emphasizes the danger of accidentally omitting a WHERE clause in an UPDATE statement.
