• ORACLE 通过连接查询更新 update select


    注意:  关键的地方是where 语句的加入. 在11G中, 如果不加11G , 或造成除匹配的行数更新为相应的值之后, 其余的会变成负数. 

        所以,执行前需要测试, 普通办法就是:  先查看需要更新的数量即连接的数量究竟有多少行, 然后update 之后多少行, 才能确定条件是否正确.

    最终完成的2个 SQL, 经过检验, 放心食用:

     1 :   通过关联表查询获取需要更新的 多列 数据更新. 注意Exists子句的必要性
    UPDATE SFISM4.R_DT_DIP_TRACEABILITY_T B
    SET (B.CURRENT_QTY, B.UPDATE_TIME, B.UPDATE_USER)=
    (SELECT DECODE(SIGN(B.CURRENT_QTY - A.KP_COUNT),
    -1,
    0,
    B.CURRENT_QTY - A.KP_COUNT),
    SYSDATE,
    :VUSER
    FROM SFIS1.C_DT_DIP_BOM_T A, SFISM4.R_MO_BASE_T C
    WHERE A.BOM_NO = B.BOM_NO
    AND B.BOM_NO = C.KEY_PART_NO
    AND A.KEY_PART_NO = B.KEY_PART_NO
    AND C.MO_NUMBER =:VBOM
    AND B.TRAC_LOT =:VTRACELOT)
    WHERE B.TRAC_LOT =:VTRACELOT
    AND EXISTS (SELECT 1
    FROM SFIS1.C_DT_DIP_BOM_T AA
    WHERE B.BOM_NO = AA.BOM_NO
    AND B.KEY_PART_NO = AA.KEY_PART_NO)
    UPDATE SFISM4.R_REEL_TRACKING_T A
    SET A.REMAIN_QTY =
    (SELECT B.REEL_QTY
    FROM SFISM4.R_MMS_STOCK_SMT_SA_DETAIL_T B
    WHERE A.REEL_ID = B.REEL_ID
    AND A.REMAIN_QTY <0
    AND A.REMAIN_QTY <> B.REEL_QTY)
    WHERE EXISTS (SELECT 1
    FROM SFISM4.R_MMS_STOCK_SMT_SA_DETAIL_T B
    WHERE A.REEL_ID = B.REEL_ID
    AND A.REMAIN_QTY <0
    AND A.REMAIN_QTY <> B.REEL_QTY);
     
    2: 如上简化版.
    UPDATE SFISM4.R_REEL_TRACKING_T A
    SET A.REMAIN_QTY =
    (SELECT B.REEL_QTY
    FROM SFISM4.R_MMS_STOCK_SMT_SA_DETAIL_T B
    WHERE A.REEL_ID = B.REEL_ID
    AND A.REMAIN_QTY <0
    AND A.REMAIN_QTY <> B.REEL_QTY)
    WHERE EXISTS (SELECT 1
    FROM SFISM4.R_MMS_STOCK_SMT_SA_DETAIL_T B
    WHERE A.REEL_ID = B.REEL_ID
    AND A.REMAIN_QTY <0
    AND A.REMAIN_QTY <> B.REEL_QTY);
     
    从一开始有了想法, 找到的相应one step by step实验过程.
     

    SQL> select * from wwm2;        --要更新的表 , 多一点. 

    TOWN                         ID
    -------------------- ----------
    222                         222
    111                         111
    ww'jj                       111
    llll                       1111
    dddd                       2222
    lllldf                      111
    lllldf                      111
    dsafdf                      111
    3435                        111
    ljjjjj                      222
    dsafdf                      111
    TOWN                         ID
    -------------------- ----------
    3435                        111
    ljjjjj                      222

    SQL> select * from wwm5;            --更新的条件表 , 少一点
    TOWN                         ID
    -------------------- ----------
    lllldf                      111
    test                       9984
    SQL> select wwm2.* from wwm2,wwm5 where wwm2.id=wwm5.id
      2  /
    TOWN                         ID
    -------------------- ----------
    111                         111
    ww'jj                       111
    lllldf                      111
    lllldf                      111
    dsafdf                      111
    3435                        111
    dsafdf                      111
    3435                        111
    8 rows selected.
    所以,每次需要更新8条数据就是正确的. 这一步是验证更新是不是对, 错的关键.!!!
    相信程序员是通过以下类似的SQL更新的,这是错误的,因为没有加WHERE
    SQL>  update wwm2 set wwm2.town=(select wwm5.town from wwm5 where wwm5.id=wwm2.id)
      2  /
    13 rows updated.
    SQL> select * from wwm2;
    TOWN                         ID
    -------------------- ----------
                                222
    lllldf                      111
    lllldf                      111
                               1111
                               2222
    lllldf                      111
    lllldf                      111
    lllldf                      111
    lllldf                      111
                                222
    lllldf                      111
    TOWN                         ID
    -------------------- ----------
    lllldf                      111
                                222
    13 rows selected.
    可以看到13条记录被更新,符合条件的更新正确,不符合条件的也更新为NULL.以下是正确的方法
    方法一:
    SQL> update wwm2
      2  set town=(select town from wwm5 where wwm5.id=wwm2.id)
      3  where id=(select wwm5.id from wwm5 where wwm5.id=wwm2.id)
      4  /
    8 rows updated.
    方法二:    与方法一道理相同,这里需要掌握EXIST的相关用法.
    SQL> update wwm2
       set town=(select town from wwm5 where wwm5.id=wwm2.id)
       where exists (select 1 from wwm5 where wwm5.id=wwm2.id)
    8 rows updated.
    方法三:
    SQL> update (select a.town atown,a.id aid,b.town btown,b.id bid from wwm2 a,wwm5 b where a.id=b.id)
      2  set atown=btown
      3  /
    set atown=btown
        *
    ERROR at line 2:
    ORA-01779: cannot modify a column which maps to a non key-preserved table
      1* alter table wwm5 add primary key (id)
    SQL> /
    Table altered.
      1  update (select a.town atown,a.id aid,b.town btown,b.id bid from wwm2 a,wwm5 b where a.id=b.id) -- 表连接之后, 更新视图的方式很好理解. 但是需要primary key.
      2*  set atown=btown
    SQL> /
    8 rows updated.
    这种方法的局限性就是需要PRIMARY 的支持.! 需要 更新被更新表有主键, 连接栏位是不是需要呢>? 
    方法四: --万金油. 但是数据库顾问说效能没有使用表连接好.
      1  declare
      2  cursor cur_wwm is select town,id from wwm5; -- 驱动 表
      3  begin
      4     for my_wwm in cur_wwm loop
      5     update wwm2 set town=my_wwm.town -- 被驱动表.
      6     where id=my_wwm.id;
      7     end loop;
      8* end;
    SQL> /
    PL/SQL procedure successfully completed.
    SQL> select * from wwm2;
    TOWN                         ID
    -------------------- ----------
    222                         222
    lllldf                      111
    lllldf                      111
    llll                       1111
    dddd                       2222
    lllldf                      111
    lllldf                      111
    lllldf                      111
    lllldf                      111
    ljjjjj                      222
    lllldf                      111
    TOWN                         ID
    -------------------- ----------
    lllldf                      111
    ljjjjj                      222
    这个方法是最灵活的了.
    方法五:
    注意,方法五只能适用于WWM5是WWM2的子集的时候. (新特性 merge Into,哎呀! 不想学.....)
      1   merge into wwm2
      2   using (select town,id from wwm5) b
      3   on (wwm2.id=b.id)
      4   when matched then update set town=b.town
      5* when not matched then insert (town,id) values (null,null)
    SQL> /
    9 rows merged.
    SQL> select * from wwm2;
    TOWN                         ID
    -------------------- ----------
                                      ---注意这个地方,被插入了一个空值.因为WWM5的ID=9984在WWM2中不能匹配,根本原因是ORACLE9必须有WHEN NOT MATCHED子句,但是ORACLE10可以不许要,也就是ORACLE10可以不写WHEN NOT MATCHED ,就不必插入NULL值了,为解决这个问题,下一步会DELETE WWM5的ID=9984,这样一来就不会执行WHEN NOT MATCHED
    222                         222
    lllldf                      111
    lllldf                      111
    llll                       1111
    dddd                       2222
    lllldf                      111
    lllldf                      111
    lllldf                      111
    lllldf                      111
    ljjjjj                      222
    TOWN                         ID
    -------------------- ----------
    lllldf                      111
    lllldf                      111
    ljjjjj                      222
    14 rows selected.
    SQL> delete from wwm5 where id=9984;
    1 row deleted.
    SQL>  1   merge into wwm2                             
    SQL>   2   using (select town,id from wwm5) b
    SQL>   3   on (wwm2.id=b.id)
    SQL>   4   when matched then update set town=b.town
    SQL>   5* when not matched then insert (town,id) values (null,null)
    SQL> /
    8 rows merged.
     
           以上就是5种关连更新的例子了,希望能给开发人员解惑.
     
    说明:如果select 子句可以返回多行记录,但返回适合where条件的记录只能是唯一的,否则将会报返回单行的select子句返回多行的错误,因为update只能跟据此处的where子句(内层where)进行相应记录的匹配更新,一次只能是一条。
        

    参考 网址: http://www.blogjava.net/Jhonney/archive/2010/06/25/324503.html



  • 相关阅读:
    linux如何安装django
    Flume报 Space for commit to queue couldn't be acquired. Sinks are likely not keeping up with sources, or the buffer size is too tight
    window下用notepad++编辑了脚本文件然后放在linux报错显示无法运行
    如何使用mysqldump备份数据库
    mysql基础小结
    复合序列分解预测实例
    互联网金融平台客户留存计算实例(月留存为例)
    互联网金融不同渠道评估实例
    互联网金融投资平台返现活动效果案例分析
    spark内核揭秘-07-DAGScheduler源码解读初体验
  • 原文地址:https://www.cnblogs.com/hijushen/p/4273929.html
Copyright © 2020-2023  润新知