SQL Merge statement

SQL Merge statement – Learn how to insert, delete and update the records into the table using single statement.

MERGE-statement made for situations when you want to do “update” – update existing rows in a table or “insert” new rows into the table.

• The MERGE statement reduces table scans and can perform the operation in parallel if required.
• SQL MERGE allows you to perform multiple INSERT, UPDATE, and DELETE operations in a single statement. Based on selection criteria specified in the MERGE statement, you can conditionally apply INSERT, UPDATE, and DELETE statements to the table.

Syntax:

MERGE INTO table_1 t1
USING table_2 t2
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET t1.column1 = t2.colum1
WHEN NOT MATCHED THEN
INSERT (t1.colummn1, t1.column2)
VALUES (t2.value1,t2.value2 );

For example,you may want to UPDATE a row if it already existed and INSERT the row if it is not already in the table. You might also want to synchronize the contents of two tables.

We have 2 tables viz STUDENT, STD_UPSERT_TBL tables.

• Student table contains STD_NO,STD_NAME,MARKS,HOD,DEPT_NO fields.
• STD_UPSERT_TBL contains STD_NO,STD_NAME,MARKS.

Creation & Insertion Includes:

Create STUDENT table

CREATE TABLE STUDENT(STD_NO NUMBER,
STD_NAME VARCHAR2(50 BYTE),
MARKS NUMBER,
HOD VARCHAR2(50 BYTE),
DEPT_NO NUMBER);

 Insert records in STUDENT

INSERT INTO STUDENT VALUES(1, 'VISWANATH', 700, 'LOSHMA',30);
INSERT INTO STUDENT VALUES(2, 'VIJAY', 800, 'MADHUSUDHAN', 20);
INSERT INTO STUDENT VALUES(3, 'SHAIK', 900, 'VEERENDRA', 10);
INSERT INTO STUDENT VALUES(4, 'LEKHAJ SRI KRISHNA', 750, 'SUDHARMA', 40);
INSERT INTO STUDENT VALUES(5, 'JAYA CHANGRA P', 850, 'SUDHARMA', 40);

Show Records

SELECT * FROM STUDENT ORDER BY 1 DESC;

Result:

Create STD_UPSERT_TBL table

CREATE TABLE STD_UPSERT_TBL( SSTD_NO NUMBER,
STD_NAME VARCHAR2(50 BYTE),
MARKS NUMBER);

Insert records in STD_UPSERT_TBL

INSERT INTO STD_UPSERT_TBL VALUES(1,'VISWANATH', 800);
INSERT INTO STD_UPSERT_TBL VALUES(3,'SHAIK', 1000);
INSERT INTO STD_UPSERT_TBL VALUES(5,'JAYA CHANGRA P', 950);
INSERT INTO STD_UPSERT_TBL VALUES(6,'SAMPATH', 400);
INSERT INTO STD_UPSERT_TBL VALUES(7,'REVANTH KUMAR', 1000);
INSERT INTO STD_UPSERT_TBL VALUES(8,'LAKSHMAN KUMAR', 6000);

Show Records

SELECT * FROM STD_UPSERT_TBL ORDER BY 1 DESC;

Result:

Query :

Update the records whose id is present in STUDENT table ,Insert the records if record does not exists.

MERGE INTO STUDENT A USING
STD_UPSERT_TBL B
ON(A.STD_NO=B.STD_NO)
WHEN MATCHED THEN
UPDATE SET A.MARKS=B.MARKS
WHEN NOT MATCHED THEN
INSERT (A.STD_NO , A.STD_NAME,A. MARKS) VALUES(B.STD_NO , B.STD_NAME,B. MARKS);

Show Records

Few points to consider while using SQL Merge:

1. Semicolon is mandatory after the merge statement.
2. When there is a MATCH clause used along with some condition, it has to be specified first among  all other WHEN MATCH clause.