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).