用一个表字段值更新另一个表字段值

77 阅读 0 评论 51 点赞

我是靠谱客的博主风中水池，最近开发中收集的这篇文章主要介绍用一个表字段值更新另一个表字段值，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

类型一：
Oracle:
另一个表的字段更新该表字段
例一、
update chgtab set ( chgtab.bm, chgtab.ks ) = ( select cl.pk_doc, cl.pk_cumdoc from alterprovider cl where chgtab.fb_oid = cl.oid ) where ( chgtab.fb_oid ) in ( select cl.oid from alterprovider cl )

UPDATE chgtab SET coutcurrtype = (SELECT tmp.coutcurrtype FROM (SELECT DISTINCT h.chgtabtid, h.coutcurrtype FROM chgtab h INNER JOIN chgtab_b b ON h.chgtabtid = b.chgtabtid where b.coutcurrtype is not null ) tmp WHERE chgtab.chgtabtid = tmp.chgtabtid)

例二、
UPDATE chgtab_bb
SET (chgtab_bb.nprice , chgtab_bb.nnotaxprice ,
      chgtab_bb.nnotaxmny , chgtab_bb.nmny ,
      chgtab_bb.ntaxmny ,
      chgtab_bb.cnexttypecode ,
      chgtab_bb.vnextcode , chgtab_bb.cnextid,
      chgtab_bb.cnextbid2 , chgtab_bb.cnextbid ,
      chgtab_bb.cnexttypecode2,
      chgtab_bb.vnextcode2, chgtab_bb.cnextid2,
      chgtab_bb.vnextrowno,
      chgtab_bb.vnextrowno2, chgtab_bb.ndiscount,
      chgtab_bb.crowno )=(select
   b.nprice,b.nnotaxprice,b.nnotaxmoney,b.nmoney,b.ntaxmny,b.cnexttypecode,b.vnextcode,b.cnextid,
   b.cnextbid2,b.cnextbid,b.cnexttypecode2,b.vnextcode2,b.cnextid2,b.vnextrowno,b.vnextrowno2,100,'1'
FROM chgtab_b b
WHERE b.chgtabt_bid = chgtab_bb.chgtabt_bid)

例三、
update chgtabtmp_bb set (cupcalbodyid,cupwarehouseid)=(select cupcalbodyid,cupwarehouseid from
chgtab_bb bb2 where chgtabtmp_bb.chgtabt_bid=bb2.chgtabt_bid and chgtabtmp_bb.cupcorpid=bb2.cupcorpid)
where chgtabt_bid||cupcorpid in (select chgtabt_bid||cupcorpid from chgtab_bb where dr=0)

特别注意：子查询中的条件必须和外部的条件相同(或者外部条件必须比子查询条件限制严格)，否则会更新错误

SQLServer:

UPDATE chgtab_bb
SET chgtab_bb.nprice = b.nprice, chgtab_bb.nnotaxprice = b.nnotaxprice,
      chgtab_bb.nnotaxmny = b.nnotaxmoney, chgtab_bb.nmny = b.nmoney,
      chgtab_bb.ntaxmny = b.ntaxmny,
      chgtab_bb.cnexttypecode = b.cnexttypecode,
      chgtab_bb.vnextcode = b.vnextcode, chgtab_bb.cnextid = b.cnextid,
      chgtab_bb.cnextbid2 = b.cnextbid2, chgtab_bb.cnextbid = b.cnextbid,
      chgtab_bb.cnexttypecode2 = b.cnexttypecode2,
      chgtab_bb.vnextcode2 = b.vnextcode2, chgtab_bb.cnextid2 = b.cnextid2,
      chgtab_bb.vnextrowno = b.vnextrowno,
      chgtab_bb.vnextrowno2 = b.vnextrowno2, chgtab_bb.ndiscount = 100,
      chgtab_bb.crowno = '1'
FROM chgtab_b b
WHERE b.chgtabt_bid = chgtab_bb.chgtabt_bid