oracle update from 问题！ 

 

update t_tmprpt_firstreplycosttime t
   set (t.firstreplytime,
       t.dealstaff,
       t.firstreplyfailcontent)
       = (select a.suggesttime,
                 a.suggester,
                 substr(a.remark,instr(a.remark,'】',1)+2)
            from t_wf_suggesthis a
           where t.serialno = a.serialno
             and t.serviceclassid = a.serviceclassid);

update t_tmprpt_firstreplycosttime t set (t.firstreplytime, t.dealstaff, t.firstreplyfailcontent) = (select a.suggesttime, a.suggester, substr(a.remark,instr(a.remark,'】',1)+2) from t_wf_suggesthis a where t.serialno = a.serialno and t.serviceclassid = a.serviceclassid);

想把t_tmprpt_firstreplycosttime 表中的3个字段数据更新为t_wf_suggesthis表中的 suggesttime, suggester,substr(a.remark,instr(a.remark,'】',1)+2)的值。条件是t表的 sreialno和serviceclassid都与a表中的相等。

问题：当我执行这条更新时候会把t表中的所有数据都更新了。

解决：oracle中没update from 这样的更新，可以考虑2种解决办法。

一,

   

update (select a.suggesttime atime,
               b.firstreplytime btime,
               a.suggester astaff,
               b.dealstaff bstaff,
               substr(a.remark,instr(a.remark,'】',1)+2) acontent,
               b.firstreplyfailcontent bcontent
          from t_wf_suggesthis a, t_tmprpt_firstreplycosttime b)
    set btime = atime,
        btaff = astaff,
        bcontent = acontent;

update (select a.suggesttime atime, b.firstreplytime btime, a.suggester astaff, b.dealstaff bstaff, substr(a.remark,instr(a.remark,'】',1)+2) acontent, b.firstreplyfailcontent bcontent from t_wf_suggesthis a, t_tmprpt_firstreplycosttime b) set btime = atime, btaff = astaff, bcontent = acontent;

这是类视图的更新方法，这也是oracle所独有的。

先把对应的数据全部抽取出来，然后更新表一样更新数据，这里需要注意的是，必须保证表的数据唯一性。

这就要求a,b两表的关联字段上都是具备唯一性的。

二,

update t_tmprpt_firstreplycosttime t
   set (t.firstreplytime,
       t.dealstaff,
       t.firstreplyfailcontent)
       = (select a.suggesttime,
                 a.suggester,
                 substr(a.remark,instr(a.remark,'】',1)+2)
            from t_wf_suggesthis a
           where t.serialno = a.serialno
             and t.serviceclassid = a.serviceclassid)
  where t.serialno in (select b.serialno
                         from t_wf_suggesthis b
                        where b.serialno = t.serialno
                          and b.serviceclassid = t.serviceclassid);

update t_tmprpt_firstreplycosttime t set (t.firstreplytime, t.dealstaff, t.firstreplyfailcontent) = (select a.suggesttime, a.suggester, substr(a.remark,instr(a.remark,'】',1)+2) from t_wf_suggesthis a where t.serialno = a.serialno and t.serviceclassid = a.serviceclassid) where t.serialno in (select b.serialno from t_wf_suggesthis b where b.serialno = t.serialno and b.serviceclassid = t.serviceclassid);

这种就是使用子查询来确定更新的数据。