11.1. Update Operations

The statements covered in this chapter modify the contents of database tables. Another statement that modifies table contents is LOAD DATA INFILE, which reads records from a data file and loads them into a table. It's discussed in Chapter 15, "Importing and Exporting Data."

For purposes of discussion here, the term "update statement" is used in a collective sense to refer to various kinds of statements that modify tables. "UPDATE statement" refers specifically to statements that begin with the UPDATE keyword. Also, keep in mind the following terminology with regard to indexes:

• The term "unique-valued index" is a generic term meaning any index that contains only unique values.

• The term "primary key" is a generic term meaning a unique-valued index that cannot contain NULL values.

• "UNIQUE index" means specifically a unique-valued index created using the keyword UNIQUE.

• "PRIMARY KEY" means specifically a unique-valued index created using the keywords PRIMARY KEY.

See Section 8.6, "Indexes," for further information about types of indexes.

Much of the discussion in this chapter uses the following table as a source of examples:

 CREATE TABLE people (     id     INT UNSIGNED NOT NULL AUTO_INCREMENT,     name   CHAR(40) NOT NULL DEFAULT '',     age    INT NOT NULL DEFAULT 0,     PRIMARY KEY (id) );