Deleting Rows

The last of the action statements that we will cover today is the DELETE statement. You have already seen how to add and insert data into a table in the database. You also learned how to modify and update that data. Now, you will see how to remove or delete rows from a table. The syntax of the DELETE statement is

DELETE <table_name | view_name>
[WHERE <condition>]

The first thing that you will probably realize is that the DELETE command does not have a prompt. Users (yourself included) are accustomed to being prompted before doing something that can destroy data or files. The "Are you sure?" message box is a common sight before performing a delete operation.

The following example deletes a row from the customer_demo table:

Delete customer_demo
Where cust_id = 3

Just as with the UPDATE statement, be very careful about how you specify which rows you want to delete by using the WHERE clause.

Ensuring a Successful Deletion

You should not turn on the NOCOUNT option when using the DELETE statement. Watch the row counts when you execute a DELETE command. Sometimes, you might delete no rows at all. If I tried to delete from the customer_demo table, and the cust_id that I specified in the WHERE clause didn't exist, I would see the following message from the server:

delete customer_demo
where cust_id = 8

(0 row(s) affected)

Issuing a DELETE that affects no rows does not cause an error. This is important to remember because in your application programs, you must be able to determine whether a delete operation completed successfully. If you intend to delete a row, issue a DELETE, and find that you haven't deleted anything, there is likely a problem in your logic or database.

Reviewing the Limits on the DELETE Statement

The same rules that apply to table joins and updates also apply to table joins and deletes. You may delete from only one table at a time, but you may join to other tables for information about which rows to delete.

In addition, you may not delete rows from any view that references more than one table.

Truncating Tables

If you plan to delete all the rows in a table, an unqualified DELETE will do the job. However, a faster way to do this is to use the TRUNCATE TABLE command as shown here:

Truncate Table customer_demo

TRUNCATE TABLE will remove all the rows in a table, but will leave the structure of the table intact. This means that you do not have to re-create the table when you use the TRUNCATE statement. The DROP statement would delete the table structure as well as the data.

Understanding Identity Columns and the TRUNCATE Statement

If there are identity columns in the table, a DELETE will remove any rows specified, but the next row that you add will continue where the identity value left off. Deleting rows from a table with identity columns means that those identity values will never be used again. The TRUNCATE statement actually resets the identity value back to its original starting value. Table 7.3 shows the differences between the DELETE, TRUNCATE, and DROP commands.