Deleting Data

To delete (remove) data from a table, the DELETE statement is used. DELETE can be used in two ways:

To delete specific rows from a table
To delete all rows from a table

Caution: Don’t Omit the **WHERE** Clause

Special care must be exercised when using DELETE because it is all too easy to mistakenly delete every row from your table. Please read this entire section on DELETE before using this statement.

The following statement deletes a single row from the Customers table (the row you added in the last lesson):

DELETE FROM Customers
WHERE cust_id = '1000000006';

This statement should be self-explanatory. DELETE FROM requires that you specify the name of the table from which the data is to be deleted. The WHERE clause filters which rows are to be deleted. In this example, only customer 1000000006 will be deleted. If the WHERE clause were omitted, this statement would have deleted every customer in the table!

Tip: Faster Deletes

If you really do want to delete all rows from a table, don’t use DELETE. Instead, use the TRUNCATE TABLE statement which accomplished the same thing but does it much quicker (because data changes are not logged).