PHP —

Deleting Data

There is an important distinction between dropping and deleting in SQL. DROP is used to remove tables or databases; DELETE is used to remove data.

The statement:

DELETE FROM customer;

deletes all data in the customer table but doesn't remove the table. In contrast, dropping the table removes the data and the table.

A DELETE statement with a WHERE clause can remove specific rows; WHERE clauses are frequently used in querying, and they are explained later in Section 3.8.3. Consider a simple example:

DELETE FROM customer WHERE cust_id = 1;

This deletes the customer with cust_id=1. Consider another example:

DELETE FROM customer WHERE surname = 'Smith';

This removes all rows for customers with the surname Smith.

Updating Data

Data can be updated using a similar syntax to that of the INSERT statement. Consider an example:

UPDATE customer SET email = lower(email);

This replaces the string values of all email attributes with the same string in lowercase. The function lower( ) is one of many functions discussed later in Section 3.9.

The UPDATE statement is also often used with the WHERE clause. For example:

UPDATE customer SET title = 'Dr' WHERE cust_id = 7;

This updates the title attribute of customer #7. Consider a second example:

UPDATE customer SET zipcode = '3001' WHERE city = 'Melbourne';

This updates the zipcode of all rows with a city value Melbourne.