--
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?🤔
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’;
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’;
(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’);
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’);
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”
