SQL Merge

Merge Statement combines the sequence of conditional INSERT and UPDATE commands in a single atomic statement, depending on the existence of a record. This operation is commonly known as Upsert functionality

Syntax

Merge into <Receiving_data_table>

Using <Base_data_table>

When Matched Then

Update Set <col1> = <value>,

<col2> = <value>

When Not Matched Then

Insert (col1, col2,….)

Values(val1, val2,….);

 

You can use merge command to perform insert and update in a single command.

Ex:

SQL> Merge into student1 s1

        Using (select *From student2) s2

        On(s1.no=s2.no)

        When matched then

        Update set marks = s2.marks

        When not matched then

        Insert (s1.no,s1.name,s1.marks)

        Values(s2.no,s2.name,s2.marks);

In the above the two tables are with the same structure but we can merge different structured tables also but the datatype of the columns should match.

Assume that student1 has columns like no,name,marks and student2 has columns like no,  name, hno, city.

SQL> Merge into student1 s1

        Using (select *From student2) s2

        On(s1.no=s2.no)

        When matched then

        Update set marks = s2.hno

        When not matched then

        Insert (s1.no,s1.name,s1.marks)

        Values(s2.no,s2.name,s2.hno);