REPLACE

This MySQL 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.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

REPLACE [LOW_PRIORITY|DELAYED] [INTO] table [(column,...)]
  VALUES ({expression|DEFAULT},...)[, (...)]

REPLACE [LOW_PRIORITY|DELAYED] [INTO] table
  SET column={expression|DEFAULT}[, ...]

REPLACE [LOW_PRIORITY|DELAYED] [INTO] table [(column,...)]
  SELECT...

Explanation

Use this MySQL 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 in MySQL has three basic formats. The first contains the values for each row in parentheses after the VALUES keyword. If the number of values and their order do not match the columns of the table named, the MySQL columns have to be listed in parentheses after the table name in the order in which the values are arranged.

Examples

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 MySQL statement is able to insert two rows without the column names being listed twice. In this example, the first row already existed before this statement was to be executed. Once it's run, the row represented by 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';

In this example, 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.