Introduction

　　Oracle merge into command, as the name implies, is “update if there is, insert if not”. This is also the core idea of ​​the merge into command. In the actual development process, we will often encounter To this kind of business that updates certain fields of one of the tables through the correlation and matching of two tables, sometimes it is necessary to deal with the business in the case of mismatch. At this time, you will find that as the amount of data in the table increases, the execution efficiency of similar business scenarios will be slower, because you need to repeatedly query the data in the two tables, and through the merge into command, only one association is required The business scenario of “update if there is, insert if there is no” can be completed, which greatly improves the execution efficiency of the statement.

Syntax

1 merge into A tableusing B table on (A table.id = B table.id)

2 when matched then --If matched, then update the A table data

3 update set A.col=B.col

4 when not matched then --No match, go to Form A Insert data

5 insert (a,b,c) values ​​(' span>a',' b','c');

Explanation: Use Table B to match Table A with the condition of Table A.id=Table B.id. When the conditions are met, Table A can be updated. Condition, you can use the inert statement to insert relevant data.

Case

merge into student.stuinfo_2018 A

using student.stuinfo B

on (A.stuid = B.stuid)

when matched then --Update data to A

 update set A.age = B.age

when not matched then --not matched to insert data into A

 insert

 (a.STUID,

 a.STUNAME,

 a.SEX,

 a.AGE,

 a.CLASSNO,

 a.STUADDRESS,

 a.GRADE,

 a.ENROLDATE,

 a.IDNUMBER)

 values

 (b.STUID,

 b.STUNAME,

 b.SEX,

 b.AGE,

 b.CLASSNO,

 b.STUADDRESS,

 b.GRADE,

 b.ENROLDATE,

 b.IDNUMBER);

1 merge into A tableusing B table on (A table.id = B table.id)

2 when matched then --If matched, then update the A table data

3 update set A.col=B.col

4 when not matched then --No match, go to Form A Insert data

5 insert (a,b,c) values ​​(' span>a',' b','c');

merge into student.stuinfo_2018 A

using student.stuinfo B

on (A.stuid = B.stuid)

when matched then --Update data to A

 update set A.age = B.age

when not matched then --not matched to insert data into A

 insert

 (a.STUID,

 a.STUNAME,

 a.SEX,

 a.AGE,

 a.CLASSNO,

 a.STUADDRESS,

 a.GRADE,

 a.ENROLDATE,

 a.IDNUMBER)

 values

 (b.STUID,

 b.STUNAME,

 b.SEX,

 b.AGE,

 b.CLASSNO,

 b.STUADDRESS,

 b.GRADE,

 b.ENROLDATE,

 b.IDNUMBER);