Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Thursday, 31 March 2016

SQL Merge

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);
Share this article :

0 comments:

Post a Comment