Inserting or Updating records using mysql REPLACE INTO

Inserting or Updating records using mysql REPLACE INTO

Inserting records into a specific table can be achieved simply by using the INSERT statement while updating records can be done by issuing an UPDATE statement. But what if you are asked for a conditional insert/update? That is, given a specific data, you need to query against a specific table to check whether it was previously inserted. If it does exist, update that record and insert otherwise. Typically, you might solve this first by firing a SELECT statement to check for the record and eventually executing UPDATE or INSERT depending on the result gathered by the the previous query. This means you need 2 statements to achieve the desired result.

Better solution? The result of the two SQL commands above can be achieved using a single query – using REPLACE INTO. REPLACE statement works the same way as the INSERT query. The only difference is the way they handle duplicate records. If there exists a unique index on a table and you attempted to insert a record that has a key value that’s previously inserted, that record will be deleted first and will insert the new record.

REPLACE syntax is also similar to the INSERT statement.

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

OR

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...

OR

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...

Below are some examples of REPLACE statements in different forms:

• Single row REPLACE

  REPLACE INTO pet (id, type, name) VALUES(1, 'Dog', 'Pluto');

• Multiple row REPLACE

  REPLACE INTO pet (id,name,age) VALUES(1, 'Dog', 'Pluto'), VALUES(2, 'Cat', 'Furry'), VALUES(3, 'Bird', 'Chloe');

• Singe row REPLACE with SET

  REPLACE INTO pet SET id = 1, type= 'Dog',  name = 'Pluto'