在db2中使用游标比较行并执行更新 [英] Compare rows using a cursor in db2 and perform update

查看:1925
本文介绍了在db2中使用游标比较行并执行更新的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有2个SQL提供了不同的结果,如下所示

表1:

-----------------
PRRFNBR  | PRNBR    
----------------
64929    | 083819                                                          
64973    | 083924
676761   | 879899    
-----------------

表2:

----------------
PRRFNBR | PRNBR

----------------
286054  | 083819                                                          
286056  | 083924  
768789  | 876122  

我需要比较表中的PRRFNBR数据并更新表2的结果与表1匹配,表2中的预期输出应如下所示:

----------------
PRRFNBR | PRNBR

----------------
64929   | 083819                                                          
64973   | 083924  
768789  | 876122     
---------------- 

I am trying to achieve this using cursors in db2 and i am new to DB2 so not having more idea about how the cursors works in db2.just wrote the cursors using the examples from Google,

DECLARE C1 CURSOR FOR  SELECT PRRFNBR,PRNBR FROM PRODUCT  WHERE PRNBR IN 
(SELECT PRNBR FROM PRODUCT WHERE PRRFNBR IN (SELECT SBEORDPRNBR FROM SUBEXCLUSION EXCEPT  SELECT SBSORDPRNBR  FROM SUBSTATS))
AND  PRRFNBR NOT IN ( SELECT SBEORDPRNBR FROM SUBEXCLUSION ) FOR UPDATE OF PRRFNBR;

DECLARE C2 CURSOR  FOR  SELECT PRRFNBR,PRNBR FROM PRODUCT WHERE PRRFNBR IN (SELECT SBEORDPRNBR FROM SUBEXCLUSION  WHERE SBEORDPRNBR IN 
(SELECT SBEORDPRNBR FROM SUBEXCLUSION EXCEPT  SELECT SBSORDPRNBR  FROM SUBSTATS) 
AND SBEORDPRNBR IN (SELECT PRRFNBR FROM PRODUCT WHERE PRNBR 
IN (SELECT PRNBR FROM PRODUCT  WHERE PRNBR IN (SELECT PRNBR FROM PRODUCT WHERE PRRFNBR IN 
(SELECT SBEORDPRNBR FROM SUBEXCLUSION EXCEPT  
SELECT SBSORDPRNBR  FROM SUBSTATS)) AND  PRRFNBR NOT IN ( SELECT SBEORDPRNBR FROM SUBEXCLUSION ))));

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO p_prrfnbr, p_prnbr;

OPEN C2;

FETCH C2 INTO :v_prrfnbr, :v_prnbr;

IF [ v_prnbr == p_prnbr ] ;

    THEN
    UPDATE SUBEXCLUSION SET SBSORDPRNBR = v_prrfnbr WHERE SBSORDPRNBR=p_prrfnbr ;
    END IF;
    END LOOP;
    close C2;
    close C1;
    END;

任何人都可以解释一下如何使用db2中的游标来实现结果?上面的光标不起作用,并且抛出以下错误,

Can anyone please explain me how to achieve the results using cursors in db2 ?. the above cursor is not working and throwing the below errors,

Wed Apr 12 10:33:34 BST 2017 [INFO] Calculating suggested substitutions
DECLARE C1 CURSOR FOR  SELECT PRRFNBR,PRNBR FROM PRODUCT  WHERE PRNBR IN (SELECT PRNBR FROM PRODUCT WHERE PRRFNBR IN (SELECT SBEORDPRNBR FROM SUBEXCLUSION EXCEPT  SELECT SBSORDPRNBR  FROM SUBSTATS)) AND  PRRFNBR NOT IN ( SELECT SBEORDPRNBR FROM SUBEXCLUSION ) FOR UPDATE OF PRRFNBR
DB20000I  The SQL command completed successfully.

DECLARE C2 CURSOR  FOR  SELECT PRRFNBR,PRNBR FROM PRODUCT WHERE PRRFNBR IN (SELECT SBEORDPRNBR FROM SUBEXCLUSION  WHERE SBEORDPRNBR IN (SELECT SBEORDPRNBR FROM SUBEXCLUSION EXCEPT  SELECT SBSORDPRNBR  FROM SUBSTATS) AND SBEORDPRNBR IN (SELECT PRRFNBR FROM PRODUCT WHERE PRNBR IN (SELECT PRNBR FROM PRODUCT  WHERE PRNBR IN (SELECT PRNBR FROM PRODUCT WHERE PRRFNBR IN (SELECT SBEORDPRNBR FROM SUBEXCLUSION EXCEPT SELECT SBSORDPRNBR  FROM SUBSTATS)) AND  PRRFNBR NOT IN ( SELECT SBEORDPRNBR FROM SUBEXCLUSION ))))
DB20000I  The SQL command completed successfully.

BEGIN
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "BEGIN".
Expected tokens may include:  "JOIN <joined_table>".  LINE NUMBER=1.
SQLSTATE=42601

OPEN C1
DB20000I  The SQL command completed successfully.

LOOP FETCH C1 INTO p_prrfnbr, p_prnbr
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "LOOP FETCH" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".
SQLSTATE=42601

OPEN C2
DB20000I  The SQL command completed successfully.

FETCH C2 INTO :v_prrfnbr, :v_prnbr
SQL0104N  An unexpected token "INTO" was found following "<identifier>".
Expected tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601

IF [ v_prnbr == p_prnbr ]
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "" was found following "BEGIN-OF-STATEMENT".
Expected tokens may include:  "<values>".  SQLSTATE=42601

THEN UPDATE SUBEXCLUSION SET SBSORDPRNBR = v_prrfnbr WHERE SBSORDPRNBR=p_prrfnbr
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "THEN" was found following "BEGIN-OF-STATEMENT".
Expected tokens may include:  "<update>".  SQLSTATE=42601

END IF
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END IF".
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

END LOOP
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END
LOOP".  Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

close C2
DB21030E  The cursor "C2" has not been opened.

close C1
DB21030E  The cursor "C1" has not been opened.


DB21007E  End of file reached while reading the command.


推荐答案

您可以使用merge语句将table1数据更新为table2 。

You can use merge statement to update table1 data into table2.

Merge into table2 a
using table1 b
on(a.PRNBR=b.PRNBR)
when matched then update set a.PRRFNBR=b.PRRFNBR;

如果要在合并之前查看结果,可以简单地用join替换使用,然后执行选择。

If you want to see the result before doing merge then you can simply replace the using with join then do select.

select * from table2 a
    join table1 b
    on(a.PRNBR=b.PRNBR)

您可以更新第三个表,如下所示

You can update that 3rd table something like below

MERGE INTO subexclusion M
USING (
SELECT T.PRNBR,P.PRRFNBR UPDATED_PRRFNBR from SESSION.TEMP2 AS T 
JOIN SESSION.TEMP1 AS P 
ON P.PRNBR=T.PRNBR
) TMP
ON(M.PRNBR=TMP.PRNBR)
WHEN MATCHED THEN UPDATE SET M.PRRFNBR=UPDATED_PRRFNBR;

这篇关于在db2中使用游标比较行并执行更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆