Oracle 关联更新

最新推荐文章于 2024-07-24 16:58:26 发布

weixin_34355881

最新推荐文章于 2024-07-24 16:58:26 发布

阅读量58

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/2611678/blog/867304

版权

2019独角兽企业重金招聘Python工程师标准>>>

场景如下：

COMM_TASK_RELATED_INFO（TASK_TYPE_ID，RELATED_ID） ctri, BOM_PARTS（PAET_ID,MOLD_NO,PART_NO,PROCESS_OBJECT） bp, SCH_WP_VNNO(WP_VNNO_ID,MOLD_NO,PART_NO,PROCESS_OBJECT) swv


2. 需要做下面更新

ctri.related_id = bp.part_id 变为 ctri.related_id = swv.wp_vnno_id


3. oarcle sql语句如下(使用游标) 来实现:

/* Formatted on 2017/3/25 11:57:03 (QP5 v5.256.13226.35510) */ SET SERVEROUTPUT ON

DECLARE CURSOR task_relate_id_cur IS SELECT CTRI.TASK_RELATED_ID,SWV.WP_VNNO_ID FROM bom_parts bp, comm_task_related_info ctri,SCH_WP_VNNO swv WHERE BP.PART_ID = CTRI.RELATED_ID and BP.MOLD_NO = SWV.MOLD_NO and BP.PART_NO = SWV.PART_NO and BP.PROCESS_OBJECT = SWV.PROCESS_OBJECT and BP.VALID_STATUS = '1' and BP.CANCEL_FLAG='0'; BEGIN FOR my_cur IN task_relate_id_cur LOOP UPDATE comm_task_related_info TRI SET TRI.RELATED_ID = my_cur.WP_VNNO_ID WHERE TRI.TASK_RELATED_ID = my_cur.TASK_RELATED_ID; END LOOP; END;

转载于:https://my.oschina.net/u/2611678/blog/867304