1、表 CompanyBgt 的数据结构是这样的:
fyearmonth branchid categoryid oborrb
其中,fyearmonth branchid categoryid为主健
=========================================================================
2、我需要把这张表中,fyearmonth从200704到200803的,channelid为“IT”的OBRORB数据全部替换成channelid是“DPIT”的数据 ?
=========================================================================
3、我试着写了一下,不知道是否正确,请各位看一下:
UPDATE CompanyBgt a
SET a.oborrb =(select oborrb from CompanyBgt b where
a.fyearmonth =b.fyearmonth
and a.branchid=b.branchid
and a.categoryid=b.categoryid
and b.channelid = 'DPIT' and a.channelid='IT')
where a.channelid='IT' and a.Fyearmonth>='200704' and a.Fyearmonth<='200803
fyearmonth branchid categoryid oborrb
其中,fyearmonth branchid categoryid为主健
=========================================================================
2、我需要把这张表中,fyearmonth从200704到200803的,channelid为“IT”的OBRORB数据全部替换成channelid是“DPIT”的数据 ?
=========================================================================
3、我试着写了一下,不知道是否正确,请各位看一下:
UPDATE CompanyBgt a
SET a.oborrb =(select oborrb from CompanyBgt b where
a.fyearmonth =b.fyearmonth
and a.branchid=b.branchid
and a.categoryid=b.categoryid
and b.channelid = 'DPIT' and a.channelid='IT')
where a.channelid='IT' and a.Fyearmonth>='200704' and a.Fyearmonth<='200803
From CompanyBgt A
Inner Join CompanyBgt B
On A.Fyearmonth = B.Fyearmonth And A.branchid = B.branchid And A.categoryid = B.categoryid
Where A.channelid = 'IT' And B.channelid = 'DPIT' And A.Fyearmonth Between '200704' And '200803'
SET a.oborrb =(select oborrb from CompanyBgt b where
a.fyearmonth =b.fyearmonth
and a.branchid=b.branchid
and a.categoryid=b.categoryid
and b.channelid = 'DPIT') --可以去掉子查詢裡面的 and a.channelid='IT'
where a.channelid='IT' and a.Fyearmonth>='200704' and a.Fyearmonth<='200803'
from CompanyBgt a inner join CompanyBgt b on a.fyearmonth =b.fyearmonth
and a.branchid=b.branchid
and a.categoryid=b.categoryid
and a.Fyearmonth>='200704' and a.Fyearmonth<='200803
and a.channelid='IT' and b.channelid = 'DPIT'
update CompanyBgt set CompanyBgt.oborrb = c.oborrb from (select * from CompanyBgt b where b.channelid = 'DPIT') c,CompanyBgt where
c.fyearmonth =CompanyBgt.fyearmonth
and c.branchid=CompanyBgt.branchid
and c.categoryid=CompanyBgt.categoryid
and CompanyBgt.channelid = 'IT' and CompanyBgt.Fyearmonth between '200704'and '200803'
你这个写法,我用过。。