Deleting Existing Data

The DELETE statement is used to delete existing rows of data from a database. Listing 4.6 shows the basic structure of the DELETE statement.

Listing 4.6. The Basic Structure of the DELETE Statement

DELETE
    [ FROM ]
        { table_name WITH ( < table_hint_limited > [ ...n ] )
         | view_name
         | rowset_function_limited
        }

        [ FROM {  < table_source > }  [ ,...n ] ]
    [ WHERE
        {  < search_condition >
        | {  [ CURRENT OF
                {  {  [ GLOBAL ] cursor_name }
                    | cursor_variable_name
                }
            ] }
        }
    ]
    [ OPTION ( < query_hint > [ ,...n ] ) ]

The DELETE statement will completely wipe out a row, so be careful! If you accidentally ran the statement DELETE Products, every row in the Products table would be deleted. Use the following DELETE statement to delete the row you just added to the Products Table. Your ProductID number might be different than 85; double check the accuracy of the number before running the following DELETE statement:

1: DELETE Products
2: WHERE ProductID = 85

You use the ProductID column as your condition because it's a Primary Key and won't be repeated anywhere. That way you won't delete anything by accident. If you run the SELECT statement from the previous section you will find that the product we added no longer shows up in the result set.