Real Life SQL and .NET: Part IV

Deleting with SQL DELETE

The third data modification statement allows you to remove one or more rows from a table.

DELETE

You use the DELETE statement to remove rows from a SQL Server table.

Syntax for the DELETE Statement DELETE [FROM] {table_name | view_name}
[WHERE clause]

The word FROM is optional, as is the WHERE clause. The following DELETE statement removes all rows from the sales table:

delete sales
(21 row(s) affected)

If you want to remove only a subset of rows in a table, than the WHERE clause allows you to qualify the rows to be removed. The WHERE conditions can include any of the conditions that you learned about in Day 10, "Data Retrieval," including relational operators (<, >, and =), and the keywords IN, LIKE, BETWEEN, and so on. The following DELETE statement removes all books with a pub_name of New Moon Books from the publisher_list table:

delete publisher_list where pub_name = `New Moon Books'

(5 row(s) affected)

DELETE Using a Lookup Table

A single DELETE statement can only remove rows from a single table. However, SQL Server does allow you to include another table in your DELETE statement for use as a lookup table. The lookup table usually appears in a subquery. In the next example, remove all titles published by New Moon Books. The titles table is the one to be modified, but it only contains the publisher ID, not the publisher name. You need to look in the publishers table to find the publisher ID from New Moon Books, which then determines the rows in the titles table for removal.

delete publisher_list where pub_name = (select pub_name from publishers where pub_id = '9956')

(1 row(s) affected)

The subquery accesses the publishers table and returns a single value for pub_name. That value is then used to determine which rows in publisher_list you are going to delete; that is, all rows with a pub_name equal to the returned value. Keep in mind that no more than one row will be returned, because pub_id is the primary key of the publishers table. If more than one row could be returned by the subquery, you would have to use IN instead of the equals symbol. This next example uses a lookup table that returns more than one value.

Remove all rows from the sales table that indicate the sale of business books. The sales table holds the title_id value of the book sold, but not its type. You must access the titles table to find which title_ids correspond to business books. Because you deleted the information in the sales table earlier in this article, you need to run the RePopSales.SQL script to repopulate the sales table before you run the following query.

Before you delete all rows from the sales table, you are going to make a temporary copy of the sales table using the SELECT INTO statements. Before you can use the SELECT INTO statements, you must mark the database option SELECT INTO/Bulkcopy to true. Please enter all the code in the following examples. Execute your query after each GO statement. The code between the /* and */ is just a comment and doesn't need to be entered.

/* Use the pubs database. */
USE pubs
GO
/* Set the database option to Select Into/Bulkcopy. */EXEC sp_dboption pubs, `select
into/bulkcopy', true
GO
/* Create the temporary table to hold your sales information. */
SELECT * INTO tmpSales FROM sales
GO
/* Use the DELETE statement to remove rows from the sales table. */
DELETE sales
GO
/* Verify that there are no rows in the sales table. */
SELECT * FROM sales
GO
stor_id ord_num ord_date qty payterms title_id
------- ------- -------- --- -------- ---------
(0 row(s) affected)

/* Reload the sales table from the tmpSales table. */
INSERT INTO sales
SELECT * FROM tmpSales
GO
/* Verify that your information has been recovered. */
SELECT * FROM sales
GO
/* You should now see 16 rows of table information. */
/* Turn off the Select Into/Bulkcopy Database option. */
EXEC sp_dboption pubs, `select into/bulkcopy', false
GO

delete sales
where title_id in
(select title_id from titles
where type = 'business')

(5 row(s) affected)

The subquery accesses the titles table and returns a list of title_id values. Those values are then used to determine which rows in sales you are going to delete--that is, all rows with a title_id equal to any of the returned values. Transact-SQL has an extension that allows you to write DELETE statements using a FROM clause containing multiple tables. This makes the DELETE appear as a join operation, although only one table is having rows deleted. The functionality provided is the same as using subqueries. The second table is used only as a lookup table. The following examples show how the DELETE statements use multiple tables in a FROM clause:

delete publisher_list where pub_name = (select pub_name from publisherswhere pub_id = `0877')

This can be rewritten as:

delete publisher_list from publisher_list, publishers where publisher_list.pub_name = publishers.pub_name and pub_id = '0877'

The choice of whether to use the subquery method or the join method depends mainly on personal preference. You may prefer the subquery method, because there is no confusion as to which table is being modified and which table is only being used as a lookup table. You should also be aware that the join method is non-ANSI standard.

continue article