|
View previous topic :: View next topic
|
| Author |
Message |
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
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 |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
| Back to top |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
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 |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
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 |
|
|
|
|
