MySql: Upsert options

I was asked a question about doing an insert to a table with a unique index and id and to always set the field active if it exists. There are a multiple ways to do this, but I reached for the one that isn't actually available in MySql because I was remembering Postgres syntax. In Postgres you would do a Merge statement and mark the conditions. My mistake got to many database formats and rules in my head.

Given a table structured like this

CREATE TABLE employees (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  cust_id INT UNSIGNED DEFAULT NULL,
  emp_name NVARCHAR(100) NOT NULL,
  active BIT DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE (emp_name)
);

Insert or update all employees named Bill to be active

Merge way to do it

MERGE INTO employees ee
USING (SELECT id FROM employee WHERE emp_name = 'Bill') AS t
ON t.id = ee.id
WHEN MATCHED THEN
  UPDATE SET active = 1
WHEN NOT MATCHED THEN
  INSERT (cust_id, emp_name, active)
  VALUES (1, 'Bill', 1);

But we don't have merge in MySql

Two statements approach

• Update if exists
• Insert with a query if doesn't exist

update employees
INNER JOIN
(
        SELECT id
        From employees
        WHERE emp_name IN
        (
            'Bill'
        )
) as u
on employees.id = u.id
SET employees.active = 1;
INSERT INTO employees (cust_id, emp_name, active)
select 1, 'Bill', 1
from employees
where emp_name <> 'Bill';

This works, but it is two statements and there is a way to do it in a single statement for clarity by relying on the uniqueness of the emp_name field

Insert into ON DUPLICATE KEY

INSERT INTO employees (cust_id, emp_name, active) VALUES (1, 'Bill', 1)
ON DUPLICATE KEY UPDATE active=1;

This is what most people would pick because it does an in place update and is one line.

Use Replace

REPLACE INTO employees (cust_id, emp_name, active)
VALUES (1, 'Bill', 1);

Using replace has the drawback of deleting and incrementing the id because it does a delete and insert to accomplish the task and that could have implications for caching and in process queries.