Deleting data from tables

As mentioned earlier, the DELETE statement removes rows from a table. This looks similar to the SELECT statement, but you don't specify a list of columns to return.

Consider the following example in which you first create a table named fruits using the following query:

CREATE TABLE fruits (id int primary key, fruit varchar(255));

Then, you insert 4 records into it:

INSERT INTO fruits VALUES (1, 'Apple'), (2, 'Pear'), (3, 'Orange'), (4, 'Carrot');

In order to check the total number of records inserted into the table, use the SELECT command:

SELECT * FROM fruits;

This will produce the following output:

Figure 3.32 – Records stored in the fruits table

Now, in order to delete a single record from a table, use the following command:

DELETE FROM fruits WHERE fruit='Carrot';

Here, you are deleting the record containing the Carrot fruit. To check whether...