Explain the usage of Merge in SQL and the usage of SQLMerge

Explain the usage of Merge in SQL and the usage of SQLMerge

A merge statement is a type of SQL statement. Available in SQL Server and Oracle databases, and unavailable in MySQL and PostgreSQL. MERGE is the new syntax of Oracle9i, used to merge update and INSERT statements. Use the MERGE statement to query another (target table) table Based on the connection conditions of one table (source table, source table) or subquery, the INSERT statement cannot be executed when UPDATE is performed on the matching connection conditions. This syntax only requires one full table scan to complete all the work, and the execution efficiency is higher than INSERT + UPDATE.

Merge is mainly used for join operations between two tables.

Merge in oracle:

The use of merge is supported from oracle 9i, and the usage of 10 Gb is improved.

Create table a (id _ integer, count _ integer); insert into a values (1, 3); insert into a values (3, 6); create table B (id _ integer, count _ integer); insert into B values (1, 7); insert into B values (2, 4); merge into a USING B ON (. id _ = B. id _) when matched then update set count _ = B. count _ +. count _/* Note the table named count _ */when not matched then insert values (B. id _, B. count _); commit; select * from;

Result:

Id _ count _
1 10
3 6
2 4

In SQL Server 2008, merge is supported:

There are two tables with the same structure: test1, test2

create table test1 (id int,name varchar(20)) go create table test2 (id int,name varchar(20)) go insert into test1(id,name) values(1,'boyi55'),(2,'51cto'),(3,'bbs'),(4,'fengjicai'),(5,'alis') insert into test2(id,name) values(1,'boyi'),(2,'51cto')

Synchronize test1 to test2, no data is inserted, and existing data is updated

Merge test2 t -- target table to be updated using test1 s -- source table on t. id = s. id -- update condition (that is, the primary key) when matched -- if the primary key matches, update then update set t. name = s. name when not matched then insert values (id, name); -- insert the unknown primary key of the target primary key. This statement must end with a semicolon

Run the following query to view the update result.

select a.id,a.name as name_1,b.name as name_2 from test1 as a,test2 as b where a.id=b.id

Id name_1 name_2
---------------------------------------------------
1 boyi55 boyi55
2 51cto 51cto
3 bbs
4 fengjicai
5 minutes later