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

类型一：
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