MYSQL MERGE STATEMENT

Merge statements are available in the Microsoft SQL Server and will be available in PostgreSQL soon with the latest version mostly available in all databases. But whether it exists in MySQL too?🤔

MySQL

This article is about MySQL Merge Statement. This statement is mostly used when we need to merge two tables based on the conditions. Let’s understand in detail about Merge.

Merge helps us to either update, insert or delete our data based on the 3 actions

(i) source and destination matched.

(ii) source and destination not matched.

(iii) not matched from either source or destination.

One of the most important things, in order to perform a merge on a table user, should have SELECT, INSERT, UPDATE and DELETE privileges on the source and target table.

Syntax for Merge

Merge source_tablename using destination_tablename

on merge_condition

when matched

then update_query

when not matched

then insert_query

when not matched by source

then delete_query;

Unfortunately, Merge is not supported in MySQL we have

(i) INSERT…ON DUPLICATE KEY UPDATE

(ii) REPLACE Statement

(i) INSERT…ON DUPLICATE KEY UPDATE

If we specify the ON DUPLICATE KEY UPDATE option in the INSERT statement and the new row causes a duplicate value in the UNIQUE or PRIMARY KEY index, MySQL performs an update to the old row based on the new values.

CREATE TABLE test (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
data VARCHAR(64) DEFAULT NULL,
ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);

INSERT INTO test(data) VALUES (‘merge’) ON DUPLICATE KEY UPDATE data = ‘merge’;

INSERT…ON DUPLICATE KEY UPDATE

CREATE TABLE test2 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
data VARCHAR(64)
DEFAULT NULL,
ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id, ts) );

INSERT INTO
test2(data,ts)
VALUES
(‘merge’,now()- interval ‘1’ second)
ON DUPLICATE KEY UPDATE data = ‘merge’;

INSERT…ON DUPLICATE KEY UPDATE

(ii) Replace Statement

Replace works exactly the same as INSERT, except if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE INDEX, the old row is deleted before the new row is inserted.

CREATE TABLE test (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
data VARCHAR(64) DEFAULT NULL,
ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id));

REPLACE INTO test VALUES (1, ‘Old’, ‘2014–08–20 18:47:00’);
REPLACE INTO test VALUES (1, ‘New’, ‘2014–08–20 18:47:42’);

Replace Statement

CREATE TABLE test2 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
data VARCHAR(64)
DEFAULT NULL,
ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id, ts) );

REPLACE INTO test2 VALUES (1, ‘Old’, ‘2014–08–20 18:47:00’);
REPLACE INTO test2 VALUES (1, ‘New’, ‘2014–08–20 18:47:42’);

Replace Statement

Thanks for the read. Do clap if find it useful😊. Please suggest topics if you want an article on any specific topic in any database.

“Keep learning and keep sharing knowledge”