Table of Contents for

MySQL in a Nutshell, 2nd Edition

Use this statement to insert new rows of data and to replace existing rows where the PRIMARY KEY or UNIQUE index key is the same as the new record being inserted. This statement requires INSERT and DELETE privileges because it is potentially a combination of both.

The LOW_PRIORITY keyword instructs the server to wait until there are no queries on the table named, including reads, and then to lock the table for exclusive use by the thread so that data may be inserted and replaced. When the statement is finished, the lock is released, automatically. For busy servers, a client may be waiting for quite a while. The DELAYED keyword will free the client by storing the statement in a buffer for processing when the table is not busy. The client won’t be given notice of the success of the statement, just that it’s buffered. If the server crashes before the changes to the data are processed, the client will not be informed and the buffer contents will be lost. The INTO keyword is optional and is a matter of style preference and compatibility with other database engines.

The REPLACE statement has three basic formats. The first contains the values for each row in parentheses after the VALUES keyword. If the order and number of values do not match the columns of the table named, the columns have to be listed in parentheses after the table name in the order in which the values are arranged. Here is an example of the REPLACE statement using this syntax:

REPLACE INTO workreq (wr_id, client_id, description)
VALUES(5768,1000,'Network Access Problem'),
(5770,1000,'Network Access Problem');

Notice that this statement is able to insert two rows without the column names being listed twice. In this example, the first row already exists before this statement is to be executed. Once it’s run, the row represented by the work request identifier 5768 is completely replaced with this data. Columns that are not included in the list of columns here are reset to their default values or to NULL, depending on the column.

The second syntax does not allow multiple rows. Instead of grouping the column names in one part of the statement and the values in another part, column names and values are given in a column=value pair. To enter the REPLACE statement from the preceding example in this format, you would have to enter the following two statements:

REPLACE INTO workreq
SET wr_id = 5768, client_id = 1000,
description = 'Network Access Problem';

REPLACE INTO workreq
SET wr_id = 5770, client_id = 1000,
description = 'Network Access Problem';

The third syntax involves a subquery, which is available as of version 4.1 of MySQL. With a subquery, data can be retrieved from another table and inserted into the table referenced in the main query for the statement. Here is an example:

REPLACE INTO workreq (wr_id, client_id, status)
SELECT wr_id, client_id, 'HOLD'
FROM wk_schedule
WHERE programmer_id = 1000;

Work requests assigned to a particular programmer are being changed to a temporarily on-hold status. The values for two of the columns are taken from the work schedule table, and the fixed string of HOLD is inserted as the value of the third column. Currently, the table for which replacement data is being inserted cannot be used in the subquery.