Previous Page
Next Page

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

Let's take a look at each of these uses.

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.


Tip

UPDATE and Security Use of the UPDATE statement can be restricted and controlled. More on this in Chapter 28, "Managing Security."


The UPDATE statement is very easy to usesome would say too easy. The basic format of an UPDATE statement is made up of three parts:

  • The table to be updated

  • The column names and their new values

  • The filter condition that determines which rows should be updated

Let's take a look at a simple example. Customer 10005 now has an email address, and so his record needs updating. The following statement performs this update:

• Input

UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

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 = 'elmer@fudd.com'

The UPDATE statement finishes with a WHERE clause that tells MySQL which row to update. Without a WHERE clause, MySQL would update all the rows in the customers table with this new email addressdefinitely not the desired effect.

Updating multiple columns requires a slightly different syntax:

• Input

UPDATE customers
SET cust_name = 'The Fudds',
    cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

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_name and cust_email will both be updated for customer 10005.

Tip

Using Subqueries in an UPDATE Statement Subqueries may be used in UPDATE statements, enabling you to update columns with data retrieved with a SELECT statement. Refer to Chapter 14, "Working with Subqueries," for more information on subqueries and their uses.


Tip

The IGNORE Keyword If your UPDATE statement updates multiple rows and an error occurs while updating one or more of those rows, the entire UPDATE operation is cancelled (and any rows updated before the error occurred are restored to their original values). To continue processing updates, even if an error occurs, use the IGNORE keyword, like this:

UPDATE IGNORE customers ...


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:

• Input

UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;

Here the NULL keyword is used to save no value to the cust_email column.


Previous Page
Next Page