Upsert (Update if exists else Insert) in SQL Server and MySQL Database

Upsert (Update if exists else Insert) in SQL Server and MySQL Database

MySQL:

Script to create the table employee:

CREATE TABLE employee
( empid int ,
name varchar(128),
PRIMARY KEY (empid)
);

insert into employee values(123,'Mark');

Following query inserts row in the employee table. If record exists with empid 456, it will update the existing record. It is necessary that empid is Primary Key or Unique Index. You can use unique index on multiple columns as well.

INSERT INTO employee(
empid, name) values (456,'Mark')
on DUPLICATE KEY UPDATE
empid = 456, name = 'John'

select * from employee

SQL Server:

Script to create the table employee:

CREATE TABLE employee
( empid int,
name varchar(128)
);

insert into employee values(123,'Mark');

Following query inserts row in the employee table. If record exists with empid 456, it will update the existing record. You can use multiple columns to match for the update to happen.

MERGE INTO employee AS SRC Using (SELECT 456) AS DEST(empid)
ON SRC.empid = DEST.empid
WHEN MATCHED THEN
UPDATE SET
name = 'Kim'
WHEN NOT MATCHED THEN
INSERT
(empid, name)
VALUES (456, 'John');

 select * from employee