merge into的妙用，mergeinto妙用

merge into的妙用，mergeinto妙用

   今天遇到一個奇葩的需求：系統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