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