在db2中使用游标比较行并执行更新 [英] Compare rows using a cursor in db2 and perform update
问题描述
我有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屋!