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
 

 

Delete the Record fetched from a cursor.

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

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Tue Apr 06, 2010 8:33 pm    Post subject: Delete the Record fetched from a cursor.
Reply with quote

Hello All,
I need to delete record corrosponding primary key from a table A and all the corrosponding records for that primary key from another table B (Primary key of Table A).
As That key is Primary for Table A , obviously that table has only 1 record, but table B hase many records for that key.

Input is taken from a flat file which contains only the KEY.
To Delete from table B, what steps i have to follow, like
1) declare a cursor (Select ____ from B where key = :Variable)
2) OPEN that Cursor
3) Fetch

Now how to delete the Record which is fetched, or i need to use DELETE in the CURSOR, and it will delete teh record at the tiem of FETCH.

Regards,
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Tue Apr 06, 2010 8:52 pm    Post subject:
Reply with quote

Hello Gaurav,

Why you want to open the cursor fetch it & then delete the record why dont directly delete using
Code:
DELETE  from B where key = :Variable
Back to top
View user's profile Send private message
GauravKudesiya
Warnings : 1

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Tue Apr 06, 2010 8:57 pm    Post subject:
Reply with quote

I am not sure wether it will work as there are number of records corrosponding to the key.

Quote:
As That KEY is Primary for Table A , obviously that table has only 1 record, but table B has many records for that KEY.


Regds,
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Tue Apr 06, 2010 8:58 pm    Post subject:
Reply with quote

Quote:
I am not sure wether it will work as there are number of records corrosponding to the key.


So ....
Back to top
View user's profile Send private message
GauravKudesiya
Warnings : 1

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Tue Apr 06, 2010 9:10 pm    Post subject:
Reply with quote

What I have to code?
Delete Query in cursor, So how will it delete the records?

This is my confusion :
At the time of OPEN all records will get deleted or I have to FETCH each record untill last, and record will get deleted at the time of FETCH?
Rgds,
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Apr 06, 2010 9:22 pm    Post subject:
Reply with quote

Gaurav,

Ekta is saying, simply DELETE the rows with simple DELETE SQL,
don't use a CURSOR. you can get the count of deleted rows from SQLERRCD(3) from the SQLCA.
Back to top
View user's profile Send private message
GauravKudesiya
Warnings : 1

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Tue Apr 06, 2010 9:46 pm    Post subject:
Reply with quote

Thanks For correcting me on the approach.
I will follow that approach...
Will get back here after following icon_smile.gif

Regards,
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Apr 07, 2010 5:00 am    Post subject:
Reply with quote

GauravKudesiya,

Quote:
I need to delete record corrosponding primary key from a table A and all the corrosponding records for that primary key from another table B

This can be done by altering the child table with
Code:
ON DELETE CASCADE

referencing the base table.

Sushanth
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: Wed Apr 07, 2010 5:37 am    Post subject: Reply to: Delete the Record fetched from a cursor.
Reply with quote

DBZ,

Cool new avatar. . . icon_smile.gif

Ich werde ein Bier mit Jhnen. . . icon_wink.gif


d
Back to top
View user's profile Send private message
GauravKudesiya
Warnings : 1

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Wed Apr 07, 2010 5:12 pm    Post subject:
Reply with quote

Hi Sushanth,
Can you explain a bit more on this..

Quote:
Code:
ON DELETE CASCADE


Regards,
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Apr 07, 2010 5:41 pm    Post subject:
Reply with quote

ON DELETE CASCADE
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Apr 07, 2010 5:42 pm    Post subject:
Reply with quote

GauravKudesiya,

Its a property of referential constraint, when a row is deleted in the parent table(tableA), the corresponding rows in the child table(tableB) will be deleted automatically.

For this childtable(tableB) needs to be altered.

Sushanth
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 JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 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 How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm
No new posts sort with previous record anatol DFSORT/ICETOOL 9 Thu Oct 06, 2016 2:36 am


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