11.3. The REPLACE Statement

11.3. The REPLACE Statement

The REPLACE statement, like INSERT, add new records to a table. The two statements have very similar syntax. The primary difference between them lies in how they handle duplicate records. Also, REPLACE does not support the ON DUPLICATE KEY UPDATE clause.

If a table contains a unique-valued index and you attempt to insert a record containing a key value that already exists in the index, a duplicate-key violation occurs and the row is not inserted. What if you want the new record to take priority over the existing one? You could remove the existing record with DELETE and then use INSERT to add the new record. However, MySQL provides REPLACE as an alternative that is easier to use and is more efficient because it performs both actions with a single statement. REPLACE is like INSERT except that it deletes old records as necessary when a duplicate unique key value is present in a new record. Suppose that you're inserting a record into the people table, which has id as a PRIMARY KEY:

• If the new record doesn't duplicate an existing id value, MySQL just inserts it.

• If the new record does duplicate an existing id value, MySQL first deletes any old records containing that value before inserting the new record.

An advantage of using REPLACE instead of an equivalent DELETE (if needed) and INSERT is that REPLACE is performed as a single atomic operation. There's no need to do any explicit table locking as there might be were you to issue separate DELETE and INSERT statements.

For a comparison of REPLACE with UPDATE, see Section 11.4, "The UPDATE Statement."

The action of REPLACE in replacing rows with duplicate keys depends on the table having a unique-valued index:

• In the absence of any such indexes, REPLACE is equivalent to INSERT because no duplicates will ever be detected.

• Even in the presence of a unique-valued index, if an indexed column allows NULL values, it allows multiple NULL values. A new record with a NULL value in that column does not cause a duplicate-key violation and no replacement occurs.

REPLACE returns an information string that indicates how many rows it affected. If the count is one, the row was inserted without replacing an existing row. If the count is two, a row was deleted before the new row was inserted. If the count is greater than two, it means the table has multiple unique-valued indexes and the new record matched key values in multiple rows, resulting in multiple duplicate-key violations. This causes multiple rows to be deleted, a situation that's described in more detail later in this section.

REPLACE statement syntax is similar to that for INSERT. The following are each valid forms of REPLACE. They're analogous to examples shown earlier in the chapter for INSERT:

• A single-record REPLACE with separate column and value lists:

   REPLACE INTO people (id,name,age) VALUES(12,'William',25); 

  
  

• A multiple-record REPLACE that inserts several rows:

   REPLACE INTO people (id,name,age) VALUES(12,'William',25),(13,'Bart',15),(14,'Mary',12); 

  
  

  The rows-affected count for a multiple-row REPLACE often is greater than two because the statement may insert (and delete) several records in a single operation.

• A single-record REPLACE with a SET clause that lists column assignments:

   REPLACE INTO people SET id = 12, name = 'William', age = 25; 

  
  

If a table contains multiple unique-valued indexes, a new record added with REPLACE might cause duplicate-key violations for multiple existing records. In this case, REPLACE replaces each of those records. The following table has three columns, each of which has a UNIQUE index:

 CREATE TABLE multikey (     i INT NOT NULL UNIQUE,     j INT NOT NULL UNIQUE,     k INT NOT NULL UNIQUE ); 

Suppose that the table has these contents:

 mysql> SELECT * FROM multikey; +---+---+---+ | i | j | k | +---+---+---+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | | 4 | 4 | 4 | +---+---+---+ 

Using REPLACE to add a record that duplicates a row in each column causes several records to be replaced with the new row:

 mysql> REPLACE INTO multikey (i,j,k) VALUES(1,2,3); Query OK, 4 rows affected (0.00 sec) mysql> SELECT * FROM multikey; +---+---+---+ | i | j | k | +---+---+---+ | 1 | 2 | 3 | | 4 | 4 | 4 | +---+---+---+ 

The REPLACE statement reports a row count of four because it deletes three records and inserts one.