An Overview of Updating Column Values

Although data is inserted into a table one or more rows at a time, it can be updated in a variety of ways, ranging from changing a single field (one column in one row) to changing all values in one or more columns across one or more rows.

The updates are accomplished with the UPDATE command. UPDATE allows you to specify new values for one or more columns in one or more tables. In its most basic form, the command specifies a new value for a given column in all rows in a single table.

UPDATE book SET cond = 'mint'; # DON'T RUN THIS 

This query changes the value of cond in every row to be 'mint', which is probably not desired. In a production database, a tiny mental hiccough or a slip of the fingers can trash every value in one or more columns across every row in a table. Usually, a WHERE clause is used to specify under what conditions a column should be updated.

A more typical use of UPDATE would be:

UPDATE book SET author = "Theodor Geisel" WHERE author = "Dr. Seuss"; 

In this example, the author column is updated in the book table, but only for the rows where column author is equal to "Dr. Seuss".

At this point, let's take a closer look at the syntax of an UPDATE query. The major parts are

• UPDATE Indicates the type of query you are running and is followed by the name of the table on which the query should run.

• SET Marks that you will be specifying a list of the names one or columns to update, along with the values (or expressions) with which the columns should be updated. Each name/value pair is separated by an equal sign (=). Multiple name/value pairs are separated with commas, as in

  SET name0='value', name1='some_other_value', ... 

• WHERE An optional WHERE clause is used to restrict on which rows the UPDATE will operate. For more information, see Chapter 4, "Retrieving Data: Simple Queries."

More complex uses of UPDATE allow you to update multiple columns simultaneously or to update columns with values that are based on formulas. For examples and information, see the following phrases.