Posted: Tue Jun 26, 2012 8:20 am Post subject: Cursor using JOIN and FOR UPDATEM OF
Hi everybody.
I need to build a cursor join 2 tables and update some columns of one of those tables but it's not working.
Code:
DECLARE CURMARCA CURSOR WITH HOLD FOR
SELECT B.NU_PESSOA,
B.NU_MATRICULA,
B.DT_FIM_VIGENCIA,
B.HH_FIM_VIGENCIA,
B.TS_ATUALIZACAO
FROM
PLD.PLDTB091_NTCA_PSA A,
PLD.PLDTB005_MRCA_CLNE B
WHERE
A.NU_DETECCAO = :PLDTB091.NU-DETECCAO
AND B.NU_TIPO_MARCA = :PLDTB005.NU-TIPO-MARCA
AND A.NU_PESSOA = B.NU_PESSOA
AND B.DT_FIM_VIGENCIA IS NULL
AND B.HH_FIM_VIGENCIA IS NULL
FOR UPDATE OF
B.NU_MATRICULA,
B.DT_FIM_VIGENCIA
B.HH_FIM_VIGENCIA,
B.TS_ATUALIZACAO
WITH UR;
Does DB2 allows that? _________________ The more I learn, the more I want to learn.
DECLARE CURMARCA CURSOR WITH HOLD FOR
SELECT B.NU_PESSOA,
B.NU_MATRICULA,
B.DT_FIM_VIGENCIA,
B.HH_FIM_VIGENCIA,
B.TS_ATUALIZACAO
FROM PLD.PLDTB005_MRCA_CLNE B
WHERE B.NU_TIPO_MARCA = :PLDTB005.NU-TIPO-MARCA
AND B.DT_FIM_VIGENCIA IS NULL
AND B.HH_FIM_VIGENCIA IS NULL
and exists (select 1
from PLD.PLDTB091_NTCA_PSA A,
where A.NU_PESSOA = B.NU_PESSOA
and A.NU_DETECCAO = :PLDTB091.NU-DETECCAO )
FOR UPDATE OF B.NU_MATRICULA, B.DT_FIM_VIGENCIA B.HH_FIM_VIGENCIA, B.TS_ATUALIZACAO
WITH UR;
or
Code:
DECLARE CURMARCA CURSOR WITH HOLD FOR
SELECT B.NU_PESSOA,
B.NU_MATRICULA,
B.DT_FIM_VIGENCIA,
B.HH_FIM_VIGENCIA,
B.TS_ATUALIZACAO
FROM PLD.PLDTB005_MRCA_CLNE B
WHERE B.NU_TIPO_MARCA = :PLDTB005.NU-TIPO-MARCA
AND B.DT_FIM_VIGENCIA IS NULL
AND B.HH_FIM_VIGENCIA IS NULL
and B.NU_PESSOA in (select A.NU_PESSOA
from PLD.PLDTB091_NTCA_PSA A,
where A.NU_DETECCAO = :PLDTB091.NU-DETECCAO )
FOR UPDATE OF B.NU_MATRICULA, B.DT_FIM_VIGENCIA B.HH_FIM_VIGENCIA, B.TS_ATUALIZACAO
WITH UR;
Joined: 01 May 2007 Posts: 44 Topics: 22 Location: DOWNTOWN BUFFALO, NY
Posted: Tue Jul 03, 2012 12:45 pm Post subject:
Decide which Table is the Main Source for the Update and use it as a Driver. If you expect 1 Row only, then Use SELECT. If > 1 row Use DECLARE CURSOR and FETCH. Use the result from that to SELECT or FETCH the rows from 2nd Table to UPDATE. If something goes wrong you'll know what the problem is easier. Plus you can put DISPLAYs to show you what's going on.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
