merge into的妙用

「爱情、让人受尽委屈。」 2022-08-07 04:43 2阅读 0赞

今天遇到一个奇葩的需求：系统A是主系统，终端PDA会上传数据到系统A。当有单据id在系统A的id能查到，则update状态；当单据id在系统A中查不到则insert。下面来做一个简单的测试。

drop table test;  
create table test(id number primary key, name varchar2(10),state number(1));  
insert into test values(1,'单据1',1);  
insert into test values(2,'单据2',1);  
insert into test values(3,'单据3',1);  
commit;  
  
**1.当一看到这个需求，我就想到用merge into，于是写出下列SQL:**  
merge into test t1  
using (select id from test where name = ?) t2  
on(t1.id = t2.id)  
when matched then  
  update set state = ?  
when not matched then  
  insert (id, name, state) values (?, ?, ?);  
这些写的问题,当select id from test where name = ?有值可以update成功，当没有值不会insert。  
  
**2. 改进一下**  
merge into test t1  
using (select id from test where name = '单据1'  
 union select -1 from dual) t2  
on(t1.id = t2.id)  
when matched then  
  update set state = 2  
when not matched then  
  insert (id, name, state) values (1, '单据1', 1);  
这些写的也有问题,当select id from test where name = ?，select id from test where name = '单据1'不能查出来数据的时候  
是ok的，当能查出来数据的时候会报主键冲突  
merge into test t1  
\*  
第 1 行出现错误:  
ORA-00001: 违反唯一约束条件 (SYS\_C0065462)  
  
**3.改进一下,保证using里面的内容查出一条记录，不管是否有值**  
select \* from test;  
       ID NAME            STATE  
\---------- ---------- ----------  
         1 单据1               1  
         2 单据2               1  
         3 单据3               1  
\--PDA的单据在系统A中有  
merge into test t1  
using (select id from test where name = '单据1'  
       union all  
       select 1 id  
         from dual  
        where not exists (select id from test where name = '单据1')) t2  
on (t1.id = t2.id)  
when matched then  
  update set state = 2  
when not matched then  
  insert (id, name, state) values (t2.id, '单据1', 1);  
\--PDA的单据在系统A中没有  
merge into test t1  
using (select id from test where name = '单据10'  
       union all  
       select 10 id  
         from dual  
        where not exists (select id from test where name = '单据10')) t2  
on (t1.id = t2.id)  
when matched then  
  update set state = 2  
when not matched then  
  insert (id, name, state) values (t2.id, '单据1', 1);  
    
 select \* from test;  
    ID NAME            STATE  
\--------- ---------- ----------  
         1 单据1               2  
         2 单据2               1  
         3 单据3               1  
        10 单据1               1

**4.如果id能够确认，那最好了，如下就变得更简洁了**

\--PDA的单据在系统A中有  
merge into test t1  
using (select 1 id from dual) t2  
on (t1.id = t2.id)  
when matched then  
  update set state = 2  
when not matched then  
  insert (id, name, state) values (t2.id, '单据1', 1);  
\--PDA的单据在系统A中没有  
merge into test t1  
using (select 10 id from dual) t2  
on (t1.id = t2.id)  
when matched then  
  update set state = 2  
when not matched then  
  insert (id, name, state) values (t2.id, '单据10', 1);