Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 8 Adding, Changing, and Deleting Data

Deleting data

delete works for both single-row and multiple-row operations.

A simplified version of delete syntax is:

delete table_name 
     where column_name = expression

Here is the complete syntax statement, which shows that you can remove rows either on the basis of specified expressions or based on data from other tables:

delete [from] 
     [[database.]owner.]{view_name | table_name}
     [where search_conditions]

delete [[database.]owner.] {table_name | view_name} 
     [from [[database.]owner.]{view_name | table_name          [(index {index_name | table_name }
               [prefetch size ] [ lru | mru])]} 
          [, [[database.]owner.] {view_name | table_name          (index {index_name | table_name }
               [prefetch size ] [lru | mru])]}]...] 
     [where search_conditions]

delete [from] 
     [[database.]owner.] {table_name | view_name}
     where current of cursor_name

The where clause specifies which rows are to be removed. When no where clause is given in the delete statement, all rows in the table are removed.

Using the from clause with delete

The optional from immediately after the delete keyword is included for compatibility with other versions of SQL. The from clause in the second position of a delete statement is a special Transact-SQL feature that allows you to select data from a table or tables and delete corresponding data from the first-named table. The rows you select in the from clause specify the conditions for the delete.

Suppose that a complex corporate deal results in the acquisition of all the Big Bad Bay City (formerly Oakland) authors and their books by another publisher. You need to remove all these books from the titles table right away, but you do not know their titles or identification numbers. The only information you have is the author's names and addresses.

You can delete the rows in titles by finding the author identification numbers for the rows that have Big Bad Bay City as the town in the authors table and using these numbers to find the title identification numbers of the books in the titleauthor table. In other words, a three-way join is required to find the rows to delete in the titles table.

The three tables are all included in the from clause of the delete statement. However, only the rows in the titles table that fulfill the conditions of the where clause are deleted. You would have to use separate delete statements to remove relevant rows in tables other than titles.

Here is the statement you need:

delete titles 
from authors, titles, titleauthor 
where titles.title_id = titleauthor.title_id 
and authors.au_id = titleauthor.au_id 
and city = "Big Bad Bay City"

The deltitle trigger in the pubs2 database prevents you from actually performing this deletion, because it does not allow you to delete any titles that have sales recorded in the sales table.

Deleting from IDENTITY columns

You can use the syb_identity keyword in a delete statement on tables containing an IDENTITY column. For example, this statement removes the row for which row_id equals 1:

delete sales_monthly
where syb_identity = 1

After you delete IDENTITY column rows, you may want to eliminate gaps in the table's IDENTITY column numbering sequence. See "Renumber the table's IDENTITY columns with bcp".


	Expand Search