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

You'll now take a look at each of these.

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.

Tip

UPDATE and Security Use of the UPDATE statement can be restricted and controlled. More on this in Chapter 28.

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:

• Input

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

Note

Table Contents, Not Tables The DELETE statement deletes rows from tables, even all rows from tables. But DELETE never deletes the table itself.

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 that accomplished the same thing but does it much quicker (trUNCATE actually drops and recreates the table, instead of deleting each row individually).