CRUD Statements  «Prev  Next»
Lesson 4 Overview of deleting data
Objective Describe how to delete data with Transact-SQL.

Deleting Data using SQL-Server

1) Syntax of the DELETE Statement in Transact-SQL for SQL Server 2019:

The DELETE statement in Transact-SQL is utilized to remove one or more rows from a table or view in SQL Server 2019. The general syntax of the DELETE statement is as follows: 
DELETE FROM [table_name]
WHERE [condition];
  • table_name: This is the name of the table from which rows will be deleted.
  • condition: This is a predicate to determine which rows should be deleted. Only the rows that satisfy this condition will be removed.

It is crucial to note that the WHERE clause is optional, but omitting it will result in all rows being deleted from the table. This highlights the importance of always verifying your DELETE statements, particularly the WHERE clause, to ensure that only the intended rows are removed.

1) Steps to Delete Data using Transact-SQL in SQL Server 2019:


To delete data using Transact-SQL in SQL Server 2019, follow these steps:
  1. Connect to the Database:
    • Open SQL Server Management Studio (SSMS) or your preferred SQL client.
    • Connect to the SQL Server instance hosting your database.
  2. Open a New Query Window:
    • Navigate to the 'File' menu, select 'New', and then choose 'Query with Current Connection' to open a new query window.
  3. Write the DELETE Statement:
    • In the query window, write your DELETE statement, specifying the table name and the condition to identify which rows should be deleted.
    • Make sure to carefully define the WHERE clause to avoid accidental deletion of unintended rows.
  4. Execute the Statement:
    • Review your DELETE statement thoroughly to ensure accuracy.
    • Execute the statement by clicking the 'Execute' button or pressing F5.
  5. Verify the Deletion:
    • After executing the DELETE statement, verify that the correct rows have been deleted.
    • You can do this by executing a SELECT statement before and after the DELETE operation to compare the data and confirm the changes.
Example: Suppose you have a table named 'Orders' with a column 'Status'. To delete all rows where the status is 'Cancelled', you would use the following T-SQL command: 
DELETE FROM Orders
WHERE Status = 'Cancelled';

This command will remove all rows from the 'Orders' table where the 'Status' column is equal to 'Cancelled'.
The DELETE statement in Transact-SQL provides a straightforward method for removing data from tables in SQL Server 2019. It is imperative to exercise caution, especially when defining the WHERE clause, to ensure that only the intended data is deleted. Due diligence in reviewing and verifying your DELETE statements is essential to maintaining data integrity and avoiding unintended data loss. Additionally, considering backup options or transaction control mechanisms can provide an extra layer of security when performing deletion operations.
To delete data in a table, you use the DELETE Transact-SQL statement. In contrast to the UPDATE statement, you do not specify column names in the DELETE statement. This is because the DELETE statement only allows you to delete an entire row of data. While you can not limit your DELETE statement to specific columns, you can use the WHERE clause to narrow your deletion to specific rows. If you do not use the WHERE clause, all of the rows in your selected table will be removed. The DELETE statement uses the following general syntax:

DELETE Transact-SQL statement

able_name is the name of the table to delete data from caption 
DELETE [FROM]
{
 table_name WITH(<table_hint> [...n])
 | view_name
 | rowset_function
}
[FROM {<table_source> [,...n]]
1) table_name is the name of the table to delete data from caption

table_hint instructs SQL Server on which index to use if you want to choose a different index from that chosen by the query optimizer. The syntax for table_hint is shown below.
2) table_hint instructs SQL Server on which index to use if you want to choose a different index from that chosen by the query optimizer. The syntax for table_hint is shown below.

A view_name is the name of the view to delete data from.
3) A view_name is the name of the view to delete data from

rowset_function is the OPENROWSET or OPENQUERY function, if used.
4) rowset_function is the OPENROWSET or OPENQUERY function, if used.

table_source is the name of the table(s), view(s), rowset function, or derived table.
5) table_source is the name of the table(s), view(s), rowset function, or derived table

search_condition is one or more conditions that determine which rows are deleted with the statement. The search_condition can also be used to specify join information if it is not specified in the FROM clause
6) search_condition is one or more conditions that determine which rows are deleted with the statement. The search_condition can also be used to specify join information if it is not specified in the FROM clause.

cursor_name|cursor_variable is the name of a declared cursor or cursor variable
7) cursor_name|cursor_variable is the name of a declared cursor or cursor variable

query_hint instructs the SQL Server optimizer to use specific options when processing the query as a whole. A query_hint follows this syntax
8) query_hint instructs the SQL Server optimizer to use specific options when processing the query as a whole. A query_hint follows this syntax

In the next lesson, you will learn how to use literal values with your INSERT, UPDATE, and DELETE statements.