1. 将BtSameTradeCustInfo表中有而PLApCustInfo表中没有的记录插入到cmis.PLApCustInfo表中 ;
相当于“并” 集的概念;
"insert into cmis.PLApCustInfo(regioncode,custid,custname,LicenseCode,state,type,operdate)"
+ " select regioncode,custid,cname,licensecode,'00','02','"
+ loginDate
+ "'"
+ " from cmis.BtSameTradeCustInfo BTCI"
+ " where not exists"
+ " ("
+ " select APC.*"
+ " from cmis.PLApCustInfo APC"
+ " where APC.custid =BTCI.custid"
+ " and APC.regionCode =BTCI.regioncode"
+ " )"
+ " and btci.regioncode='315';
结构为:: insert into Table(.....)
select from
where not exists
select from where
2. 对于在BtSameTradeCustInfo中客户状态为已销户的客户,在PLApMemberCustInfo中修改其状态为已销户:03
"update cmis.PLApMemberCustInfo APMC"
+ " set memberstate='03'"
+ " where exists "
+ " ("
+ " select apc.* "
+
"from cmis.BtSameTradeCustInfo BTCI,cmis.PLApCustInfo APC"
+ " where APC.custid =BTCI.custid"
+ " and APC.regionCode =BTCI.regioncode"
+ " and APC.custid =APMC.custid"
+ " and APC.regioncode =APMC.regioncode"
+ " and substr(BTCI.status,1,1)='1'"
结构为:
update Tabel set where
select from
相当于“并” 集的概念;
"insert into cmis.PLApCustInfo(regioncode,custid,custname,LicenseCode,state,type,operdate)"
+ " select regioncode,custid,cname,licensecode,'00','02','"
+ loginDate
+ "'"
+ " from cmis.BtSameTradeCustInfo BTCI"
+ " where not exists"
+ " ("
+ " select APC.*"
+ " from cmis.PLApCustInfo APC"
+ " where APC.custid =BTCI.custid"
+ " and APC.regionCode =BTCI.regioncode"
+ " )"
+ " and btci.regioncode='315';
结构为:: insert into Table(.....)
select from
where not exists
select from where
2. 对于在BtSameTradeCustInfo中客户状态为已销户的客户,在PLApMemberCustInfo中修改其状态为已销户:03
"update cmis.PLApMemberCustInfo APMC"
+ " set memberstate='03'"
+ " where exists "
+ " ("
+ " select apc.* "
+
"from cmis.BtSameTradeCustInfo BTCI,cmis.PLApCustInfo APC"
+ " where APC.custid =BTCI.custid"
+ " and APC.regionCode =BTCI.regioncode"
+ " and APC.custid =APMC.custid"
+ " and APC.regioncode =APMC.regioncode"
+ " and substr(BTCI.status,1,1)='1'"
结构为:
update Tabel set where
select from