SQL 與 Informix 關聯資料更新

update mrp.pmc_file set pmc02=b.VendorCode
,pmc03=b.VendorStName collate Chinese_PRC_CI_AS
,pmc04=b.VendorFullName collate Chinese_PRC_CI_AS
FROM mrp.pmc_file a,(
select * from OpenQuery(KSTiptop,'
select a.pmn04,max(b.pmm09) VendorCode,max(c.pmc03) VendorStName,max(c.pmc081) VendorFullName
from pmn_file a,pmm_file b,pmc_file c
where a.pmn01=b.pmm01 and b.pmm09=c.pmc01
and b.pmm04=(select max(pmm04) from pmm_file s,pmn_file t where s.pmm01=t.pmn01 and t.pmn04=a.pmn04
and s.pmm04 between today-30 and today
group by t.pmn04)
and b.pmm04 between today-30 and today
group by a.pmn04')) b where a.pmc01=b.pmn04 collate Chinese_PRC_CI_AS

insert into mrp.pmc_file
select pmn04,VendorCode,VendorStName collate Chinese_PRC_CI_AS
,VendorFullName collate Chinese_PRC_CI_AS
from OpenQuery(KSTiptop,'
select a.pmn04,max(b.pmm09) VendorCode,max(c.pmc03) VendorStName,max(c.pmc081) VendorFullName
from pmn_file a,pmm_file b,pmc_file c
where a.pmn01=b.pmm01 and b.pmm09=c.pmc01
and b.pmm04=(select max(pmm04) from pmm_file s,pmn_file t where s.pmm01=t.pmn01 and t.pmn04=a.pmn04
and s.pmm04 between today-30 and today
group by t.pmn04)
and b.pmm04 between today-30 and today
group by a.pmn04') a
where not exists (select '*' from mrp.pmc_file t where t.pmc01=a.pmn04 collate Chinese_PRC_CI_AS)