merge into usage

To put it simply, it is judged whether there is data in the table that meets the on() condition. If it is, the data will be updated. If not, the data will be inserted.

There is a table T with two fields a and b. We want to do Insert/Update in table T. If the conditions are met, then Update the value of b in T, otherwise insert a record in T. In Microsoft's SQL syntax, a simple sentence is sufficient. The syntax in SQL Server is as follows: (Recommended learning: MySQL video tutorial)　

if exists(select 1 from T where T.a='1001' )
update T set T.b=2 Where T.a='1001'
else
insert into T(a,b) values('1001',2);

But it is obvious This syntax can only change one statement for SQL, and Oracle cannot use it. So there is Merge into (a feature introduced by Oracle 9i) syntax

merge into 目标表 a
 
using 源表 b
 
on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)  
 
when matched then update set a.更新字段=b.字段
 
when  not matched then insert into a(字段1,字段2……)values(值1,值2……)

"In a Merge that has both Insert and Update syntax In the statement, the total number of Insert/Update records is the number of records in the "source table" in the Using statement.

Source table b may be a table with a different table structure than a, it may be a temporary table with the same table structure, or it may be data we have assembled ourselves.

For more MySQL-related technical articles, please visit the MySQL Database Graphic Tutorial column to learn!

The above is the detailed content of merge into usage. For more information, please follow other related articles on the PHP Chinese website!