-
To delete specific rows from a table
-
To delete all rows from a table
You'll now take a look at each of these.
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.
I already stated that UPDATE is very easy to use. The good (and bad) news is that DELETE is even easier to use.
The following statement deletes a single row from the customers table:
DELETE FROM customers WHERE cust_id = 10006;
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 10006 will be deleted. If the WHERE clause were omitted, this statement would have deleted every customer in the table.
DELETE takes no column names or wildcard characters. DELETE deletes entire rows, not columns. To delete specific columns use an UPDATE statement (as seen earlier in this tutorial).
The DELETE statement deletes rows from tables, even all rows from tables. But DELETE never deletes the table itself.
Faster Deletes If you really do want to delete all rows from a table, don't use DELETE. Instead, use the TRUNCATE TABLE statement that accomplished the same thing but does it much quicker (TRUNCATE actually drops and recreates the table, instead of deleting each row individually).