Chapter 7. Update Statements

This chapter covers the following exam topics:

• The INSERT and REPLACE statements

• The UPDATE statement

• The DELETE and TRUNCATE statements

• Handling duplicate key values

• Using ORDER BY and LIMIT with UPDATE and DELETE statements

Questions on the material in this chapter make up approximately 10% of the exam.

This chapter discusses SQL statements that modify the contents of database tables:

• INSERT adds new records to a table.

• REPLACE is similar to INSERT, except that if a new record contains a value that duplicates a unique key value in an existing record, it deletes the old record first.

• UPDATE modifies existing table records.

• DELETE and TRUNCATE TABLE remove records from a table. DELETE can remove some or all records; TRUNCATE TABLE completely empties a table.

Another statement that modifies table contents is LOAD DATA INFILE, which reads records from a datafile and loads them into a table. It's discussed in Chapter 9, "Importing and Exporting Data."

Note that 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:

• "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.

• The term "unique-valued index" is a generic term meaning any index that contains only unique values. A UNIQUE index or a PRIMARY KEY is a unique-valued index.

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

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,

    age    INT NOT NULL,

    PRIMARY KEY (id)

);