After completing this chapter, you will be able to:
Delete information from your database
Manage deletions and control row deletions
Delete data using ADO.NET
There will be occasions when you need to delete some or all of the information in your database. You may need to remove a record inserted by error, or you may decide to delete information physically from a table when the user wants to delete some records. Alternatively, you may need to remove old information from an historical table. In all of these cases, you will use the DELETE statement.
The basic DELETE sentence is:
DELETE FROM [<ServerName>.][<DataBseName>.]<SchemaName>.<TableName> WHERE <Conditions>
If you execute the DELETE sentence with a connection to the current database, you can simply use the <SchemaName>.<TableName> form without including the server and database names . You can use the DELETE statement without a WHERE condition, but typically you do not want to remove all of the content from a table.
If you want to remove all of the rows from a table, you can use the TRUNCATE TABLE statement instead of the DELETE statement. It will perform the task quickly, but you must be sure that there are no relationships from or to the table.
To create a DELETE sentence using SQL Server Management Studio, perform the following steps.
Right-click on the desired table in Object Explorer.
Choose Script Table As.
Choose Delete To.
Choose New Query Editor Window. SQL Server Management Studio will create a template for you.
If you work through the procedures in this chapter using the AdventureWorks database, it is important to back up your database first.
You can use any or all columns of a table to create the WHERE condition. You can enhance the performance of the WHERE condition by using the following guidelines.
Use the Primary.Key column/s in the condition.
Use columns that are part of an index.
These are suggestions only, for you can delete rows by using any column of a table in the WHERE condition. It is also not necessary to use a column from an index.
The following is an example of a simple DELETE sentence:
DELETE FROM [AdventureWorks].[Person].[Address] WHERE AddressID=1
The WHERE condition can be used with any of the conditional arguments it accepts to delete information, including those that establish ranges of data, or with logical combinations of AND, OR, and NOT.
If you need to delete a set of records that depends on several conditions, it is better to write a SELECT query with the same filters first to check that you will be deleting only the necessary records.
Review the following example (included in the sample files as \Ch11\AdvWorks\Queries\Sample01.sql) in which different WHERE filtering operators are utilized.
DELETE FROM Production.Product WHERE (MakeFlag = 1) AND (ReorderPoint BETWEEN 200 AND 600) AND (SellStartDate < CONVERT(DATETIME, '2000-01-01 00:00:00', 102))
You may need to remove rows from one table depending on conditions applied to another table or tables. The best way to accomplish this is to use the IN clause, which is applied over a field included on one index. The following example removes the history information about those products matching the same conditions we used in the previous sample (included in the sample files as \Ch11\AdvWorks\Queries\Sample02.sql).
DELETE FROM [AdventureWorks].[Production].[ProductInventory] WHERE ProductID in ( SELECT ProductID FROM Production.Product WHERE (MakeFlag = 1) AND (ReorderPoint BETWEEN 200 AND 600) AND (SellStartDate < CONVERT(DATETIME, '2000-01-01 00:00:00', 102)) )
Another way to remove these rows is to create a WHERE condition that is applied over relationships between the tables, but to specify the table from which you want to delete the rows. This code is included in the sample files as \Ch11\AdvWorks\Queries\Sample03.sql.
DELETE Production.ProductInventory FROM Production.ProductInventory INNER JOIN Production. Product ON Production.ProductInventory.ProductID = Production. Product .ProductID WHERE (Production. Product .MakeFlag = 1) AND (Production. Product .ReorderPoint BETWEEN 200 AND 600) AND (Production. Product .SellStartDate < CONVERT(DATETIME, '2000-01-01 00:00:00', 102))
When you design a database, you can establish rules about how the deletion process occurs. In a relationship between tables, you can configure the relationship to delete in cascade during the deletion process of a row.
Assume that the Purchase Order business rule establishes that it is possible to delete cancelled purchase orders. In this scenario, you can use data definition language (DDL) to define that when some process deletes a row from the PurchaseOrderHeader table, the corresponding rows in the PurchaseOrderDetails table will automatically be deleted as well. The DDL would look something like the following (included in the sample files as \Ch11\AdvWorks\Queries\Sample04.sql):
ALTER TABLE Purchasing.PurchaseOrderDetail ADD CONSTRAINT FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID FOREIGN KEY ( PurchaseOrderID ) REFERENCES Purchasing.PurchaseOrderHeader ( PurchaseOrderID ) ON UPDATE NO ACTION ON DELETE CASCADE GO
If you use SQL Server Management Studio to create your database objects, pay attention to the Insert And Update Specification option when creating a relationship, as shown in Figure 11-1.
Be careful when using the Cascade feature. Deleting rows with a dependency on other tables can be dangerous, and important information can be lost. Referential integrity will prevent you from losing the rows that you need. When you enable the Cascade Delete feature, you must be sure that none of your essential data will be lost.