oracle与sql两表关联更新

oracle与sql两表关联更新

 

Sql代码 www.2cto.com  

update T_DestoryBillsDetail  set T_DestoryBillsDetail.wg02=b.wg02,T_DestoryBillsDetail.oldwg10=b.wg10  from T_DestoryBillsDetail a INNER JOIN  gdgl b  on( a.wg00=b.wg00 and a.atype=b.atype collate Chinese_PRC_90_CI_AI) where   a.bid=1211071407220801000   

 

Sql代码  

update a set a.wg02=b.wg02,a.oldwg10=b.wg10  from T_DestoryBillsDetail a,gdgl b where a.wg00=b.wg00 and a.atype=b.atype and a.bid=1211071407220801000   

 

Sql代码  

update a set a.wg02=b.wg02,a.oldwg10=b.wg10  from T_DestoryBillsDetail a,gdgl b where (a.wg00=b.wg00 and a.atype=b.atype  collate Chinese_PRC_90_CI_AI)  and  a.bid=1211071407220801000  

 

sql 2005或者其他低版本 数据库，好像经常报无法解决 equal to 操作中 "Chinese_PRC_CI_AS" 和 "Chinese_PRC_90_CI_AI" 之间的排序规则冲突。 

所以加上collate Chinese_PRC_90_CI_AI 

 

oracle 写法 

Sql代码

update T_DestoryBillsDetail a set (wg02,oldwg10)=(select wg02,wg10 from gdgl b where a.wg00=b.wg00 and a.atype=b.atype ) where exists  (select 1 from gdgl b where b.wg00=a.wg00 and b.atype=a.atype) and a.bid=1211071407220801000