View previous topic :: View next topic
|
Author |
Message |
dharmaraok
New User
Joined: 10 Mar 2011 Posts: 45 Location: india
|
|
|
|
I want to delete the records in a table in chunks by using cursor.
Cursor normally fetches the records one by one and delete the records one by one. but I need to fetch the records in Chunk - Say Example fetch the 100 records at one go and delete the records at one GO.
Is there any syntax available in the Cursor. I have checked the manuals but i did not got any such of syntax.
Could you please help us for my Query |
|
Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
If you want to delete multiple rows then you can simply write:
Code: |
DELETE FROM TABLE
WHERE COLUMN > :WS-VAR1
AND COLUMN < :WS-VAR2 |
The number of rows deleted will depend on the range you provide with WS-VAR1 and WS-VAR2.
If you want multiple chunks to be deleted then just keep executing this code with different values of WS-VAR1 and WS-VAR2.
Why do you think you want to use the Cursor?
. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Quote: |
Cursor normally fetches the records one by one and delete the records one by one. but I need to fetch the records in Chunk - Say Example fetch the 100 records at one go and delete the records at one GO.
Is there any syntax available in the Cursor. I have checked the manuals but i did not got any such of syntax.
|
This is what you need to for what you ask (Multi ROW Fetch). Read the last bullet point..
Code: |
WHERE CURRENT OF cursor-name Identifiesthe cursor to be used in the delete operation. cursor-name must identify a declared cursor as explained in the description of the DECLARE CURSOR statement in DECLARE CURSOR. If the DELETE statement is embedded in a program, the DECLARE CURSOR statement must include select-statement rather than statement-name.
The table or view named must also be named in the FROM clause of the SELECT statement of the cursor, and the result table of the cursor must be capable of being deleted. For an explanation of read-only result tables, see Read-only cursors. Note that the object of the DELETE statement must not be identified as the object of the subquery in the WHERE clause of the SELECT statement of the cursor.
If the cursor is ambiguous and the plan or package was bound with CURRENTDATA(NO), DB2 might return an error to the application if DELETE WHERE CURRENT OF is attempted for any of the following:
•A cursor that is using block fetching
•A cursor that is using query parallelism
•A cursor that is positioned on a row that has been modified by this or another application process
When the DELETE statement is executed, the cursor must be open and positioned on a row or rowset of the result table.
•If the cursor is positioned on a single row, that row is the one deleted, and after the deletion the cursor is positioned before the next row of its result table. If there is no next row, the cursor positioned after the last row.
•If the cursor is positioned on a rowset, all rows corresponding to the rows of the current rowset are deleted, and after the deletion the cursor is positioned before the next rowset of its result table. If there is no next rowset, the cursor positioned after the last rowset.
A positioned DELETE must not be specified for a cursor that references a view on which an instead of delete trigger is defined, even if the view is an updatable view.
|
How to do Multi Row Fetch
How to Delete Multiple Row |
|
Back to top |
|
|
dharmaraok
New User
Joined: 10 Mar 2011 Posts: 45 Location: india
|
|
|
|
Hi ,
Can any one provide the sample pgm for
Multi Row Fetch and after that Delete Multiple Row |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
No unfortunately and that’s not how it works and even if someone does and gives you the code , how should it help you to learn anything anyway when you don’t try anything yet by yourself ?
You got to spend time reading the above links and figure out the coding by yourself. You have got it enough to start with and spend time to do research on internet and find out ready made programs or code which satisfies what you looking for in case if that’s simpler.
On the other note if you got to be too lazy then implement what RahulG31 has suggested and he has given the code too. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
Back to top |
|
|
|