Removing data
SQL commands also allow for the deletion of a record in the database. This syntax is exactly the same as the UPDATE
or
SELECT
statement except using the DELETE
keyword.
For the this tutorial, the following table will be used again:
Product_ID | type | quantity |
---|---|---|
32 | Apple | 10 |
45 | Orange | 91 |
78 | Apricots | 25 |
141 | Pear | 34 |
It's very important that the WHERE
clause is used in conjunction with the DELETE
statement to restrict what data is removed. Following the same pattern as shown in the WHERE
clause example with
fruits, the following statement will remove all apples and pears which have a quantity less than 9.
DELETE FROM `fruits` WHERE (type="Apple" OR type="Pear") AND quantity < 9
Clearing a table
Sometimes it is necessary to clear an entire table. This can be done using a syntax similar to the SELECT *
syntax:
DELETE * FROM `fruits`
However, SQL also permits the omission of the *
here:
DELETE FROM `fruits`