|
View previous topic :: View next topic
|
| Author |
Message |
Susanta
Active User
Joined: 17 Nov 2009
Posts: 126
Location: India
|
|
|
|
Hi
I need to fetch rows using mutil fetch from a table then to update the table with new values.
if i use rowset fetch to pull 10 rows each fetch , in that case
can i update all those 10 rows using a single update query using current of cursor statement , provided need to update different values in set statement for a specific column for each of the rows.
Can anybody help on this please.
Thanks,
Susanta |
|
| Back to top |
|
 |
prino
Senior Member
Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania
|
|
|
|
| And what does a DB2 question do in a COBOL forum? |
|
| Back to top |
|
|
Susanta
Active User
Joined: 17 Nov 2009
Posts: 126
Location: India
|
|
|
|
sorry.. mistakenly put it in cobol.
dont have access, could some one please move it to DB2 section. |
|
| Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
Good question; got me thinking but I believe the answer is no for UPDATE statement.
You have only two options- Either update the entire rowset using CURRENT OF clause with same value or update a specific row using FOR ROW n clause.
Also, n cannot be a host variable. In the manual you will see example that uses dynamic SQL to achieve same effect as if using host variable.
With that said, MERGE may be able to do what you are trying to achieve.
MERGE can work with ROWSET array! Of course, the INSERT part should never execute in your case.
Disclaimer: I haven't coded this myself but theoretically, it seems possible to do what you are trying to achieve with MERGE statement. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
If you want to update or delete a specific row or set of rows within the rowset, you will need the clause FOR ROW abc OF ROWSET added to the UPDATE or DELETE WHERE CURRENT OF cursor SQL statement. 'abc' can be an integer value or a host variable. It's available since DB2 V8.
Somthing of this nature will allow to update the specific row:
| Code: |
UPDATE Some_Table
SET CURSOR1 = 5
WHERE CURRENT OF cursor-name
FOR ROW row-number OF ROWSET |
|
|
| Back to top |
|
|
Susanta
Active User
Joined: 17 Nov 2009
Posts: 126
Location: India
|
|
|
|
Thanks agkshirsagar and Anuj.
I tried to run the program with
FOR ROW 1 oF ROWSET .. it runs fine. but updates only first record.
But when i try to run the program with
FOR ROW abc OF ROWSET when abc defined as s9(4) comp ..
it gives -803 sql error.
(called the update sql incrementing the abc)
What can be the reason.
Thanks. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
Are you supplying different values for update, for the column/s in question?
-803 SQLCODE occurs, as the definition goes, when the table that is the object of the INSERT or UPDATE operation is constrained by UNIQUE INDEX in the INDEX SPACE indexspace-name to have unique values in certain columns... |
|
| Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
| Anuj Dhawan wrote: |
| 'abc' can be an integer value or a host variable |
Thanks, Anuj for clarifying that for OP. I had implied it can't be a host variable thinking it was like with FETCH FIRST n clause where n can't be a host variable. 
Technically, ROW n type of update is a 'Positioned Update' which is not 'Multi Row Update' that OP desired.
MERGE statement is worth a try.. |
|
| Back to top |
|
|
Susanta
Active User
Joined: 17 Nov 2009
Posts: 126
Location: India
|
|
|
|
Agkshirsagar /Anuj,
Elaborating it in detail.
| Code: |
01 result array.
05 r1 picx(5) occurs 10 times value spaces.
05 r2 picx(10) occurs 10 times value spaces.
05 r3 picx(10) occurs 10 times value spaces.
01 var.
05 n pic s9(4) comp value +0.
exec sql
declare cursor csr with rowset positioning with hold for
select c1,c2,c3 from tbl1
end-exec
exec sql
oepn csr
end-exec
exec sql
fetch next rowset from csr for 10 rows
into :r1;:r2,:r3
end-exec
move 1 to n
perform until n>10
some processing to modify r2(n) and r3(n) value.
add 1 to n
end-perform
move 1 to n
perform until n > 10
exec sql
update tbl1
set c2=:r2 , c2=r3
where current of csr for row n of rowset
end-exec
add 1 to n
end-perform
|
in table tbl1 c1 is primary key. my assumtion is in every call to the update query, the set caluse will get the corresponding nth occurences of the array of r2 and r3.
will it work like this? again mentioning the subscripts inside update sql like below gives syntax error.
| Code: |
move 1 to n
perform until n > 10
exec sql
update tbl1
set c2=:r2(n) , c2=r3(n)
where current of csr for row n of rowset
end-exec
add 1 to n
end-perform
|
Please let know your thoughts. never used merge..not sure if merge will work or not in my version.
Thanks |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
The appropriate manual (for your release/version) will tell you. |
|
| Back to top |
|
|
|
|
