The REPLACE Statement

The REPLACE statement works just like INSERT, except that if a row already exists in the table with the same PRIMARY KEY value as the new data, the new row replaces it. Therefore, REPLACE never causes a PRIMARY KEY violation.

The syntax for REPLACE is just like INSERT, or you can use the alternative syntax with SET, if you prefer. The following examples both perform the same operation: to replace the MINI product with a new description, weight, and price.

MariaDB> REPLACE INTO products
 -> (product_code, name, weight, price)
 -> VALUES ('MINI', 'Mini produt', '1.25', 3.99);
Query OK, 2 rows affected (0.00 sec)

MariaDB> REPLACE INTO products
 -> SET product_code = 'MINI',
 -> name = 'Mini product',
 -> weight = 1.25,
 -> price = 3.99;
Query OK, 2 rows affected (0.00 sec)

Replacing Rows The actual behavior of REPLACE is to execute a DELETE operation with the given primary key value and then perform an INSERT; it does not perform an UPDATE. The output from the previous examples shows two rows affected because two operations took place: one DELETE and one INSERT.