IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Positioned Deletes/Updates using cursors with sub selects?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Auryn

New User


Joined: 11 Jan 2006
Posts: 75
Location: Lower Saxony (DE)

PostPosted: Thu Apr 21, 2022 2:05 pm
Reply with quote

Hi there everybody,

I'd like to fetch a cursor declared like
Code:
SELECT my_id
     , crit_1
     , col_3
     , col_4
     , col_5
     , (SELECT COUNT(*) FROM child_1_tab WHERE par_1_id = par.my_id) AS chd_1_cnt
     , (SELECT COUNT(*) FROM child_2_tab WHERE par_2_id = par.my_id) AS chd_2_cnt
  FROM parent_table    par
 WHERE crit_1 = 'X'


After fetching one of these rows and cross-checking some of these data (i.e. col_3 with other (operational) criteria I'd like to delete some of these rows respectively update some columns (i.e. col_4) of some of these rows.

My question is:
When deleting or updating, can I use a positioned delete or update WHERE CURSOR OF ...?
Or do I have to use a searched operation WHERE my_id = :host-variable?

And when updating using the WHERE CURSOR OF clause, do all the columns I'd like to modify have to be listed in the column list ot the cursors select?

Additional information: All the child tables foreign key delete rules are defined as ON DELETE CASCADE.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2945
Location: NYC,USA

PostPosted: Fri Apr 22, 2022 8:20 pm
Reply with quote

DELARE CURSOR WITH HOLD if you plan to use frequest COMMITS.

Please read more-
ibmmainframes.com/about66851.html
www.ibm.com/docs/en/db2/11.5?topic=sseisa-performing-positioned-update-delete-operations-in-sqlj-application
Back to top
View user's profile Send private message
Auryn

New User


Joined: 11 Jan 2006
Posts: 75
Location: Lower Saxony (DE)

PostPosted: Fri Apr 22, 2022 9:08 pm
Reply with quote

Hey Rohit,

thanks for your reply. That's a very good hint.
But this was not the question.

The main question is:
Do positioned updates and deletes (with the WHERE CURSOR OF clause) work with the subselects that determine some aggregated information from depending tables)?
Back to top
View user's profile Send private message
Auryn

New User


Joined: 11 Jan 2006
Posts: 75
Location: Lower Saxony (DE)

PostPosted: Wed Apr 27, 2022 2:15 pm
Reply with quote

Hey everybody,

sorry for confusing.
I recently recognized I made a little mistake writing my Question some days ago.
Was asking how positioned operations with WHERE CURSOR OF work.
This was wrong. Meant WHERE CURRENT OF

Hope, now you all understand my question
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts SORT deletes the SORTOUT file DFSORT/ICETOOL 8
No new posts IAM updates are not commiting randomly JCL & VSAM 2
No new posts Abend deletes last record written ABENDS & Debugging 10
No new posts Any limit on usage of cursors ? DB2 1
No new posts Is there drawbacks in using DB2 rowse... DB2 11
Search our Forums:

Back to Top