Deleting data from SQL Server database tables

You use the DELETE statement to delete unwanted data from SQL Server database tables.

To execute the DELETE statement, the user must at least have the DELETE permission assigned on the target table.

The basic syntax for a DELETE statement is as follows:

[WITH <common_table_expression> [,...n]]
DELETE
[TOP (expression) [percent]]
[FROM] table_name | view_name | rowset_function
[WITH table_hint]
<outputclause>
[FROM table_source]
WHERE search_conditions

The following are the arguments of a DELETE statement:

  • WITH: This keyword specifies the CTE that you can reference in your DELETE statement
  • TOP: Specify this keyword to only delete a specific number or percent of rows from the top of the query
  • table_name | view_name | rowset function | common_table_expression: This specifies the name of the table, view, rowset function, or CTE containing the data that you want to delete
  • FROM: This keyword specifies the name of the table, view, or CTE from which you are deleting the data
  • WHERE: This keyword specifies the search condition to identify which records you want to delete
  • WITH table_hints: You use this keyword to specify one or more table hints for the destination table
  • output_clause: You use this to identify the rows affected by a DELETE statement

The DELETE statement examples

The following are the two examples of the DELETE statement.

Example 1 – deleting a single row

The following DELETE statement deletes all rows from the dbo.CustomProducts table in which the ProductCategory column value is Components:

USE [AdventureWorks2012];
GO

DELETE FROM [dbo].[CustomProducts]
WHERE [ProductCategory] = N'Components';
GO

Example 2 – deleting all rows

Execute the following DELETE statement to delete all rows from the dbo.CustomProducts table:

USE [AdventureWorks2012];
GO

DELETE FROM [dbo].[CustomProducts];
GO
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.217.254.118