Database Reference
In-Depth Information
▼
Input
DELETE FROM customers
WHERE cust_id = 10006;
▼
Analysis
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
delete 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, which accomplishes the
same thing but does it much more quickly (
TRUNCATE
actually drops and re-creates the
table, instead of deleting each row individually).
Data
The
UPDATE
and
DELETE
statements used in the previous sections all have
WHERE
clauses, and there is a good reason for this. If you omit the
WHERE
clause, the
UPDATE
or
DELETE
is applied to every row in the table. In other
words, if you execute an
UPDATE
without a
WHERE
clause, every row in the
table is updated with the new values. Similarly if you execute
DELETE
without
a
WHERE
clause, all the contents of the table are deleted.
Here are some best practices that many SQL programmers follow:
Never execute an
UPDATE
or a
DELETE
without a
WHERE
clause unless
you really do intend to update and delete every row.
■
