Updating Data

To update (modify) data in a table the UPDATE statement is used. UPDATE can be used in two ways:

To update specific rows in a table
To update all rows in a table

Caution: Don’t Omit the WHERE Clause

Special care must be exercised when using UPDATE, because it is all too easy to mistakenly update every row in your table. Please read this entire section on UPDATE before using this statement.

Let’s take a look at a simple example. Customer 1000000005 has no e-mail address on file and now has an address, and so that record needs updating. The following statement performs this update:

UPDATE Customers
SET cust_email = '[email protected]'
WHERE cust_id = '1000000005';

The UPDATE statement always begins with the name of the table being updated. In this example, it is the Customers table. The SET command is then used to assign the new value to a column. As used here, the SET clause sets the cust_email column to the specified value:

SET cust_email = '[email protected]'

The UPDATE statement finishes with a WHERE clause that tells the DBMS which row to update. Without a WHERE clause, the DBMS would update all the rows in the Customers table with this new e-mail address—definitely not the desired effect.

Updating multiple columns requires a slightly different syntax:

UPDATE Customers
SET cust_contact = 'Sam Roberts',
    cust_email = '[email protected]'
WHERE cust_id = '1000000006';

When updating multiple columns, only a single SET command is used, and each column = value pair is separated by a comma. (No comma is specified after the last column.) In this example, columns cust_contact and cust_email will both be updated for customer 1000000006.

To delete a column’s value, you can set it to NULL (assuming the table is defined to allow NULL values). You can do this as follows:

UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';

Here the NULL keyword is used to save no value to the cust_email column. That is very different from saving an empty string—an empty string (specified as “) is a value, whereas NULL means that there is no value at all.

Guidelines for Updating and Deleting Data

The UPDATE and DELETE statements used in the previous section all have WHERE clauses, and there is a very good reason for this. If you omit the WHERE clause, the UPDATE or DELETE will be applied to every row in the table. In other words, if you execute an UPDATE without a WHERE clause, every row in the table will be updated with the new values. Similarly if you execute DELETE without a WHERE clause, all the contents of the table will be deleted