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

最新推荐文章于 2025-01-24 21:47:23 发布

zhsusn

最新推荐文章于 2025-01-24 21:47:23 发布

阅读量2.6k

点赞数

CC 4.0 BY-SA版权

文章标签： sqlserver oracle join null

本文链接：https://blog.csdn.net/zhsusn/article/details/1721557

本文介绍如何使用Oracle和SQL Server中的子查询来更新表中的多个字段，包括不同表间字段的同步更新方法。通过实例展示了复杂的数据同步场景下，如何确保更新操作的准确性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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