// table1 跟 table2 schema 不一定要一模一樣!!
create table "informix".table1
  (
    empid char(5) not null ,
    name char(20),
    salary integer
  ) extent size 16 next size 16 lock mode page;
create unique index "informix".ix100_1 on "informix".table1 (empid)
    using btree  in dbspace1;

create table "informix".table2
  (
    empid char(5) not null ,
    name char(20),
    salary integer
  ) extent size 16 next size 16 lock mode page;
create unique index "informix".ix101_1 on "informix".table2 (empid)
    using btree  in dbspace1;

//table1  3筆 data
insert into table1 values("00001","aaaaa",1000) ;
insert into table1 values("00003","ccccc",3000) ;
insert into table1 values("00005","eeeee",5000) ;

//table2  5筆 data
insert into table2 values("00001","11111",1234) ;
insert into table2 values("00002","22222",2234) ;
insert into table2 values("00003","33333",3234) ;
insert into table2 values("00004","44444",4234) ;
insert into table2 values("00005","55555",5234) ;

//用 table2 來修正 table1 的資料,當 table1.empid = table2.empid 時,
//用 table2的資料更新 table1(條件就是 t1.empid = t2.empid)
//資料只存在table2,不存在 table1的話....就 將 該筆資料新增至 table1
merge  into table1 t1
using table2 as t2
on t1.empid = t2.empid
when matched then update
  set (t1.name,t1.salary) = (t2.name,t2.salary)
when not matched then insert
(t1.empid,t1.name,t1.salary) values
(t2.empid,t2.name,t2.salary)


select * from table1

empid name   salary
00001 11111    1234
00003 33333    3234
00005 55555    5234
00002 22222    2234
00004 44444    4234


PS.....可以用 delete 語法....
MERGE INTO customer c
   USING ext_customer e
  ON c.customer_num=e.customer_num
  WHEN MATCHED THEN
DELETE ;


PS2....可以用以下語法...
MERGE INTO customer c
   USING (SELECT * from ext_customer e1, orders e2
  WHERE e1.customer_num=e2.customer_num ) e
   ON c.customer_num=e.customer_num AND c.fname=e.fname
  AND c.lname=e.lname
   WHEN NOT MATCHED THEN
  INSERT (c.fname, c.lname, c.company, c.address1, c.address2,
  c.city, c.state, c.zipcode, c.phone)
  VALUES (e.fname, e.lname, e.company, e.address1, e.address2,
  e.city, e.state, e.zipcode, e.phone)
   WHEN MATCHED THEN
  UPDATE SET c.fname = e.fname,
   c.lname = e.lname,
   c.company = e.company,
   c.address1 = e.address1,
   c.address2 = e.address2,
   c.city = e.city,
   c.state = e.state,
   c.zipcode = e.zipcode,
   c.phone = e.phone ;

PS3.....merge 語法從 IDS 11.50.UC5 以上版本開始提供 !!! 我們用的版本是 11.50.UC6 !!

PS4....沒試過 跨  IDS server , but ....要用的太複雜,建議盤後的需求試試看即可!

全站熱搜

hedgezzz 發表在 痞客邦 留言(1) 人氣()