更新将B表的数据更新到A表中的某个字段的sql(根据条件)

最新推荐文章于 2023-12-18 15:49:18 发布

原创最新推荐文章于 2023-12-18 15:49:18 发布 · 1.3w 阅读

5 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

8 篇文章

订阅专栏

该博客介绍了如何使用SQL语句将B表（t_org_admin）中的FResponPositionID字段值，根据特定条件（fisleaf=1, FIsOUSealUp=0）更新到A表（T_ORG_OUPartAdmin）中相应的记录。首先展示了一个直接的UPDATE语句，然后为了确保安全，创建了数据备份，并提供了更安全的更新脚本，通过SELECT...INTO创建临时表并进行更新操作，最后验证了操作成功。" 111027419,8036407,SpringCloud日志路径配置问题及解决,"['springcloud', 'spring', 'logback', 'logging', '配置问题']

update A set FResponPositionID=(select FResponPositionID from B where A.funitid=B.fid

and B.fisleaf=1 and B.FIsOUSealUp=0 and B.FResponPositionID <>A.FResponPositionID);

实际过程中：

将T_ORG_OUPartAdmin表中的FResponPositionID更新为对应的t_org_admin 中的FResponPositionID，具体如下：

update T_ORG_OUPartAdmin set FResponPositionID=(select FResponPositionID from t_org_admin where T_ORG_OUPartAdmin.funitid=t_org_admin.fid
and t_org_admin.fisleaf=1 and t_org_admin.FIsOUSealUp=0 and t_org_admin.FResponPositionID <>T_ORG_OUPartAdmin.FResponPositionID);

上述SQL会执行全部，再次实现的sql如下：

select a.fresponpositionid name, b.fresponpositionid as newname

from T_ORG_OUPartAdmin a, t_org_admin b where a.funitid=b.fid and ((a.FResponPositionID <> b.FResponPositionID)

or (b.FResponPositionID is not null and a.FResponPositionID is null ));

-----备份--------

select * into dml_T_ORG_OUPartAdmin_190819 from T_ORG_OUPartAdmin where fid in (select a.fid

from T_ORG_OUPartAdmin a, t_org_admin b where a.funitid=b.fid and ((a.FResponPositionID <> b.FResponPositionID)

or (b.FResponPositionID is not null and a.FResponPositionID is null )));

---执行脚本使扩展行政组织对应的负责人与行政组织的部门负责人一致；

update (select a.fresponpositionid name, b.fresponpositionid as newname

from T_ORG_OUPartAdmin a, t_org_admin b where a.funitid=b.fid and ((a.FResponPositionID <> b.FResponPositionID)

or (b.FResponPositionID is not null and a.FResponPositionID is null )))