MERGE Statement
The MERGE statement performs an if-then logic. It lets you insert a new record when a condition is met, or it lets you update an existing record when it isn’t met. The MERGE statement lets you perform the insert or update in a single SQL statement. It’s very handy but has a couple things that might trip you up.
The MERGE statement is implemented in Oracle and SQL Server but MySQL uses an ON DUPLICATE KEY clause. As qualified it is possible to write an ON DUPLICATE KEY statement that appears successful but may duplicate rows where no unique database-level constraint prevents it.
Oracle
Oracle’s MERGE statement is very thorough but it takes a LOT of typing. The upside from my perspective is that it enforces that you must use the primary key column to avoid writing a duplicate row. The same can be said for SQL Server but not for the ON DUPLICATE KEY clause used by MySQL.
Here’s a quick example that you can cut and paste into your environment for Oracle Database 11g. The only differences between the Oracle implementation are PL/SQL to T-SQL specific conditional table drop syntax, and the FROM dual component in the subquery.
-- Conditionally drop the table and sequence. BEGIN FOR i IN (SELECT NULL FROM user_tables WHERE TABLE_NAME = 'SYSTEM_USER') LOOP EXECUTE IMMEDIATE 'DROP TABLE system_user CASCADE CONSTRAINTS'; END LOOP; FOR i IN (SELECT NULL FROM user_sequences WHERE sequence_name = 'SYSTEM_USER_S1') LOOP EXECUTE IMMEDIATE 'DROP SEQUENCE system_user_s1'; END LOOP; END; / -- Create the table. CREATE TABLE system_user ( system_user_id NUMBER CONSTRAINT pk_su PRIMARY KEY , system_user_name VARCHAR2(20) CONSTRAINT nn_su_1 NOT NULL , system_user_group_id NUMBER CONSTRAINT nn_su_2 NOT NULL , system_user_type NUMBER CONSTRAINT nn_su_3 NOT NULL , first_name VARCHAR2(20) , middle_name VARCHAR2(10) , last_name VARCHAR2(20) , created_by NUMBER CONSTRAINT nn_su_4 NOT NULL , creation_date DATE CONSTRAINT nn_su_5 NOT NULL , last_updated_by NUMBER CONSTRAINT nn_su_6 NOT NULL , last_update_date DATE CONSTRAINT nn_su_7 NOT NULL); -- Create the sequence with a default start value of 1. CREATE SEQUENCE system_user_s1; -- Insert new row. INSERT INTO system_user VALUES ( system_user_s1.nextval , 'SYSADMIN' , 1 , 1 , NULL , NULL , NULL , 1 , SYSDATE - 1 , 1 , SYSDATE - 1); -- Insert new or merge into existing row. MERGE INTO system_user target USING (SELECT 1 AS system_user_id , 'SYSADMIN' AS system_user_name , 1 AS system_user_group_id , 1 AS system_user_type , 'Samuel' AS first_name , 'the' AS middle_name , 'Lamanite' AS last_name , 1 AS created_by , SYSDATE AS creation_date , 1 AS last_updated_by , SYSDATE AS last_update_date FROM dual) SOURCE ON (target.system_user_id = SOURCE.system_user_id) WHEN MATCHED THEN UPDATE SET first_name = 'Samuel' , middle_name = 'the' , last_name = 'Lamanite' , last_updated_by = 1 , last_update_date = SYSDATE WHEN NOT MATCHED THEN INSERT ( target.system_user_id , target.system_user_name , target.system_user_group_id , target.system_user_type , target.first_name , target.middle_name , target.last_name , target.created_by , target.creation_date , target.last_updated_by , target.last_update_date ) VALUES ( SOURCE.system_user_id , SOURCE.system_user_name , SOURCE.system_user_group_id , SOURCE.system_user_type , SOURCE.first_name , SOURCE.middle_name , SOURCE.last_name , SOURCE.created_by , SOURCE.creation_date , SOURCE.last_updated_by , SOURCE.last_update_date ); |
-- Conditionally drop the table and sequence. BEGIN FOR i IN (SELECT null FROM user_tables WHERE table_name = 'SYSTEM_USER') LOOP EXECUTE IMMEDIATE 'DROP TABLE system_user CASCADE CONSTRAINTS'; END LOOP; FOR i IN (SELECT null FROM user_sequences WHERE sequence_name = 'SYSTEM_USER_S1') LOOP EXECUTE IMMEDIATE 'DROP SEQUENCE system_user_s1'; END LOOP; END; / -- Create the table. CREATE TABLE system_user ( system_user_id NUMBER CONSTRAINT pk_su PRIMARY KEY , system_user_name VARCHAR2(20) CONSTRAINT nn_su_1 NOT NULL , system_user_group_id NUMBER CONSTRAINT nn_su_2 NOT NULL , system_user_type NUMBER CONSTRAINT nn_su_3 NOT NULL , first_name VARCHAR2(20) , middle_name VARCHAR2(10) , last_name VARCHAR2(20) , created_by NUMBER CONSTRAINT nn_su_4 NOT NULL , creation_date DATE CONSTRAINT nn_su_5 NOT NULL , last_updated_by NUMBER CONSTRAINT nn_su_6 NOT NULL , last_update_date DATE CONSTRAINT nn_su_7 NOT NULL); -- Create the sequence with a default start value of 1. CREATE SEQUENCE system_user_s1; -- Insert new row. INSERT INTO system_user VALUES ( system_user_s1.nextval , 'SYSADMIN' , 1 , 1 , NULL , NULL , NULL , 1 , SYSDATE - 1 , 1 , SYSDATE - 1); -- Insert new or merge into existing row. MERGE INTO system_user target USING (SELECT 1 AS system_user_id , 'SYSADMIN' AS system_user_name , 1 AS system_user_group_id , 1 AS system_user_type , 'Samuel' AS first_name , 'the' AS middle_name , 'Lamanite' AS last_name , 1 AS created_by , SYSDATE AS creation_date , 1 AS last_updated_by , SYSDATE AS last_update_date FROM dual) source ON (target.system_user_id = source.system_user_id) WHEN MATCHED THEN UPDATE SET first_name = 'Samuel' , middle_name = 'the' , last_name = 'Lamanite' , last_updated_by = 1 , last_update_date = SYSDATE WHEN NOT MATCHED THEN INSERT ( target.system_user_id , target.system_user_name , target.system_user_group_id , target.system_user_type , target.first_name , target.middle_name , target.last_name , target.created_by , target.creation_date , target.last_updated_by , target.last_update_date ) VALUES ( source.system_user_id , source.system_user_name , source.system_user_group_id , source.system_user_type , source.first_name , source.middle_name , source.last_name , source.created_by , source.creation_date , source.last_updated_by , source.last_update_date );
It’s possible that you could run into an ORA-30926 error. The problem is explained in this blog entry.
MySQL
The downside of MySQL’s ON DUPLICATE KEY UPDATE statement is that it takes it lets you use an override signature on the INSERT that then creates a new row when it shouldn’t. I logged a bug when I discovered that behavior on 5/25/2009 but they don’t quite agree that it’s a bug. The upside is that the typing is MUCH shorter provided you remember to use the auto increment key column in the SELECT clause.
The correct way to do this in MySQL is shown in the next example.
-- Conditionally drop table. DROP TABLE IF EXISTS system_user; -- Create table. CREATE TABLE system_user ( system_user_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , system_user_name CHAR(20) NOT NULL , system_user_group_id INT NOT NULL , system_user_type INT NOT NULL , first_name CHAR(20) , middle_name CHAR(10) , last_name CHAR(20) , created_by INT NOT NULL , creation_date DATE NOT NULL , last_updated_by INT NOT NULL , last_update_date DATE NOT NULL); -- Insert new row. INSERT INTO system_user ( system_user_name , system_user_group_id , system_user_type , created_by , creation_date , last_updated_by , last_update_date ) VALUES ('SYSADMIN' , 1 , 1 , 1 , DATE_SUB(UTC_DATE(),INTERVAL 1 DAY) , 1 , DATE_SUB(UTC_DATE(),INTERVAL 1 DAY)); -- Insert new or merge into existing row. INSERT INTO system_user VALUES ( 1 ,'SYSADMIN' , 1 , 1 ,'Samuel' ,'the' ,'Lamanite' , 1 , NOW() , 1 , NOW()) ON DUPLICATE KEY UPDATE first_name = 'Samuel' , middle_name = 'the' , last_name = 'Lamanite' , last_updated_by = 1 , last_update_date = NOW(); |
-- Conditionally drop table. DROP TABLE IF EXISTS system_user; -- Create table. CREATE TABLE system_user ( system_user_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , system_user_name CHAR(20) NOT NULL , system_user_group_id INT NOT NULL , system_user_type INT NOT NULL , first_name CHAR(20) , middle_name CHAR(10) , last_name CHAR(20) , created_by INT NOT NULL , creation_date DATE NOT NULL , last_updated_by INT NOT NULL , last_update_date DATE NOT NULL); -- Insert new row. INSERT INTO system_user ( system_user_name , system_user_group_id , system_user_type , created_by , creation_date , last_updated_by , last_update_date ) VALUES ('SYSADMIN' , 1 , 1 , 1 , DATE_SUB(UTC_DATE(),INTERVAL 1 DAY) , 1 , DATE_SUB(UTC_DATE(),INTERVAL 1 DAY)); -- Insert new or merge into existing row. INSERT INTO system_user VALUES ( 1 ,'SYSADMIN' , 1 , 1 ,'Samuel' ,'the' ,'Lamanite' , 1 , NOW() , 1 , NOW()) ON DUPLICATE KEY UPDATE first_name = 'Samuel' , middle_name = 'the' , last_name = 'Lamanite' , last_updated_by = 1 , last_update_date = NOW();
This ensures that the auto increment column values are matched. It returns what you’d expect, a single row inserted into or updated in the table.
mysql> SELECT * FROM system_user\G *************************** 1. ROW *************************** system_user_id: 1 system_user_name: SYSADMIN system_user_group_id: 1 system_user_type: 1 first_name: Samuel middle_name: the last_name: Lamanite created_by: 1 creation_date: 2009-05-24 last_updated_by: 1 last_update_date: 2009-05-25 1 ROW IN SET (0.00 sec) |
mysql> SELECT * FROM system_user\G *************************** 1. row *************************** system_user_id: 1 system_user_name: SYSADMIN system_user_group_id: 1 system_user_type: 1 first_name: Samuel middle_name: the last_name: Lamanite created_by: 1 creation_date: 2009-05-24 last_updated_by: 1 last_update_date: 2009-05-25 1 row in set (0.00 sec)
In case you didn’t read the bug, here’s a quick example of what not to do!.
-- Conditionally drop the table. DROP TABLE IF EXISTS system_user; -- Create the table. CREATE TABLE system_user ( system_user_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , system_user_name CHAR(20) NOT NULL , system_user_group_id INT NOT NULL , system_user_type INT NOT NULL , first_name CHAR(20) , middle_name CHAR(10) , last_name CHAR(20) , created_by INT NOT NULL , creation_date DATE NOT NULL , last_updated_by INT NOT NULL , last_update_date DATE NOT NULL); -- Insert new row. INSERT INTO system_user ( system_user_name , system_user_group_id , system_user_type , created_by , creation_date , last_updated_by , last_update_date ) VALUES ('SYSADMIN' , 1 , 1 , 1 , DATE_SUB(NOW(),INTERVAL 1 DAY) , 1 , DATE_SUB(NOW(),INTERVAL 1 DAY)); -- Insert new or merge into existing row. INSERT INTO system_user ( system_user_name , system_user_group_id , system_user_type , first_name , middle_name , last_name , created_by , creation_date , last_updated_by , last_update_date ) VALUES ('SYSADMIN' , 1 , 1 ,'Samuel' ,'the' ,'Lamanite' , 1 , NOW() , 1 , NOW()) ON DUPLICATE KEY UPDATE first_name = 'Samuel' , middle_name = 'the' , last_name = 'Lamanite' , last_updated_by = 1 , last_update_date = UTC_DATE(); |
-- Conditionally drop the table. DROP TABLE IF EXISTS system_user; -- Create the table. CREATE TABLE system_user ( system_user_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , system_user_name CHAR(20) NOT NULL , system_user_group_id INT NOT NULL , system_user_type INT NOT NULL , first_name CHAR(20) , middle_name CHAR(10) , last_name CHAR(20) , created_by INT NOT NULL , creation_date DATE NOT NULL , last_updated_by INT NOT NULL , last_update_date DATE NOT NULL); -- Insert new row. INSERT INTO system_user ( system_user_name , system_user_group_id , system_user_type , created_by , creation_date , last_updated_by , last_update_date ) VALUES ('SYSADMIN' , 1 , 1 , 1 , DATE_SUB(NOW(),INTERVAL 1 DAY) , 1 , DATE_SUB(NOW(),INTERVAL 1 DAY)); -- Insert new or merge into existing row. INSERT INTO system_user ( system_user_name , system_user_group_id , system_user_type , first_name , middle_name , last_name , created_by , creation_date , last_updated_by , last_update_date ) VALUES ('SYSADMIN' , 1 , 1 ,'Samuel' ,'the' ,'Lamanite' , 1 , NOW() , 1 , NOW()) ON DUPLICATE KEY UPDATE first_name = 'Samuel' , middle_name = 'the' , last_name = 'Lamanite' , last_updated_by = 1 , last_update_date = UTC_DATE();
This script ends up inserting two rows when only one should be present, as shown:
mysql> SELECT * FROM system_user\G *************************** 1. ROW *************************** system_user_id: 1 system_user_name: SYSADMIN system_user_group_id: 1 system_user_type: 1 first_name: NULL middle_name: NULL last_name: NULL created_by: 1 creation_date: 2009-05-24 last_updated_by: 1 last_update_date: 2009-05-24 *************************** 2. ROW *************************** system_user_id: 2 system_user_name: SYSADMIN system_user_group_id: 1 system_user_type: 1 first_name: Samuel middle_name: the last_name: Lamanite created_by: 1 creation_date: 2009-05-25 last_updated_by: 1 last_update_date: 2009-05-25 2 ROWS IN SET (0.02 sec) |
mysql> SELECT * FROM system_user\G *************************** 1. row *************************** system_user_id: 1 system_user_name: SYSADMIN system_user_group_id: 1 system_user_type: 1 first_name: NULL middle_name: NULL last_name: NULL created_by: 1 creation_date: 2009-05-24 last_updated_by: 1 last_update_date: 2009-05-24 *************************** 2. row *************************** system_user_id: 2 system_user_name: SYSADMIN system_user_group_id: 1 system_user_type: 1 first_name: Samuel middle_name: the last_name: Lamanite created_by: 1 creation_date: 2009-05-25 last_updated_by: 1 last_update_date: 2009-05-25 2 rows in set (0.02 sec)
SQL Server
SQL Server under research and development …
Written by michaelmclaughlin
October 8th, 2009 at 8:44 pm
Posted in
5 Responses to 'MERGE Statement'
Subscribe to comments with RSS or TrackBack to 'MERGE Statement'.
My only idea on how to improve this would be to break the example merge into pieces. i.e. select, from, the joins, insert, and give a brief explanation on what they do in relation to the merge and link to other parts of the blog for more information from previous labs. I think that would help me a lot more with understanding how to write them. Let me know if that sounds useful or I am just missing too much from previous labs.
Andrew Thimmig
9 Mar 10 at 10:44 pm
Also, if you could include an example with Group By with an explanation or a link to further information it would be even more beneficial.
Andrew Thimmig
9 Mar 10 at 11:30 pm
Did you check this post?
michaelmclaughlin
10 Mar 10 at 1:09 am
This webpage doesn’t display properly on my iphone 4 – you might wanna try and fix that
Benita Nwankwo
1 Mar 17 at 4:03 am
Benita, I use a plug-in, and apparently it no longer supports iPhone4.
michaelmclaughlin
23 Dec 17 at 3:10 pm