MySQL REPLACE : insert or update a line

The MySQL syntax REPLACE is used to insert a new line if the provided does not exist or to update it else.

Insert a line with replace
mysql> REPLACE INTO test (id, name) VALUES (1, 'nico');
Query OK, 1 ROW affected (0.02 sec)

mysql> SELECT * FROM test;
+----+------+
| id | name |
+----+------+
|  1 | nico |
+----+------+
1 ROW IN SET (0.00 sec)

Update a line with replace
mysql> REPLACE INTO test (id, name) VALUES (1, 'nico2');
Query OK, 2 ROWS affected (0.01 sec)

mysql> SELECT * FROM test;
+----+-------+
| id | name  |
+----+-------+
|  1 | nico2 |
+----+-------+
1 ROW IN SET (0.01 sec)

You can notice in this last example that 2 lines were updated. Indeed, REPLACE does not actually UPDATE the line. If there is a conflict on the key, it DELETE the line before INSERT a new one. Be careful about the side effects: delete in cascade, trigger and so on...

To verify that, we will create triggers for UPDATE, DELETE and INSERT on the table "test". These triggers will insert a line in the table "tigger_test".

Create triggers with MySQL
DELIMITER $$
CREATE TRIGGER trigger_insert AFTER INSERT ON test
FOR EACH ROW
  BEGIN
    INSERT INTO trigger_test(action) VALUES('insert');
  END $$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER trigger_update AFTER UPDATE ON test
FOR EACH ROW
  BEGIN
    INSERT INTO trigger_test(action) VALUES('update');
  END $$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER trigger_delete AFTER DELETE ON test
FOR EACH ROW
  BEGIN
    INSERT INTO trigger_test(action) VALUES('delete');
  END $$
DELIMITER ;

After replaying the two previous tests, here is the content of the table "trigger_test":

REPLACE does a DELETE then an INSERT
mysql> SELECT * FROM trigger_test;
+--------+
| action |
+--------+
| INSERT | -- test 1
| DELETE | -- test 2
| INSERT |
+--------+
3 ROWS IN SET (0.01 sec)

REPLACE is not limited to primary keys, it can work with all unique key constraints. If a unique key constraint is broken and the primary key is an auto-increment, it will be incremented.

REPLACE on a UNIQUE constraint
mysql> CREATE TABLE IF NOT EXISTS `test` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(60) NOT NULL,
  `last_visit` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
Query OK, 0 ROWS affected (0.04 sec)


mysql> INSERT INTO test(name, last_visit) VALUES('nico', NOW());
Query OK, 1 ROW affected (0.01 sec)


mysql> SELECT * FROM test;
+----+------+---------------------+
| id | name | last_visit          |
+----+------+---------------------+
|  1 | nico | 2022-10-12 14:06:05 |
+----+------+---------------------+
1 ROW IN SET (0.00 sec)


mysql> REPLACE INTO test(name, last_visit) VALUES('nico', NOW());
Query OK, 2 ROWS affected (0.01 sec)


mysql> SELECT * FROM test;
+----+------+---------------------+
| id | name | last_visit          |
+----+------+---------------------+
|  2 | nico | 2022-10-12 14:08:07 |
+----+------+---------------------+
1 ROW IN SET (0.01 sec)