Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Update multiple rows with differentValues for specific colum

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Susanta

Active User


Joined: 17 Nov 2009
Posts: 118
Location: India

PostPosted: Thu Aug 22, 2013 4:41 pm    Post subject: Update multiple rows with differentValues for specific colum
Reply with quote

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
View user's profile Send private message

prino

Active Member


Joined: 07 Feb 2009
Posts: 982
Location: Oostende, Belgium

PostPosted: Thu Aug 22, 2013 4:58 pm    Post subject:
Reply with quote

And what does a DB2 question do in a COBOL forum?
Back to top
View user's profile Send private message
Susanta

Active User


Joined: 17 Nov 2009
Posts: 118
Location: India

PostPosted: Thu Aug 22, 2013 5:52 pm    Post subject:
Reply with quote

sorry.. mistakenly put it in cobol.
dont have access, could some one please move it to DB2 section.
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 685
Location: Earth

PostPosted: Thu Aug 22, 2013 7:33 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Aug 23, 2013 5:09 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Susanta

Active User


Joined: 17 Nov 2009
Posts: 118
Location: India

PostPosted: Fri Aug 23, 2013 5:39 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Aug 23, 2013 5:49 pm    Post subject:
Reply with quote

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
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 685
Location: Earth

PostPosted: Fri Aug 23, 2013 7:47 pm    Post subject:
Reply with quote

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. icon_redface.gif

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
View user's profile Send private message
Susanta

Active User


Joined: 17 Nov 2009
Posts: 118
Location: India

PostPosted: Mon Aug 26, 2013 1:55 am    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Aug 26, 2013 3:00 am    Post subject:
Reply with quote

Hello,

The appropriate manual (for your release/version) will tell you.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us