// 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 ....要用的太複雜,建議盤後的需求試試看即可!
留言列表