Home » SQL & PL/SQL » SQL & PL/SQL » Alternative for UPDATE CURSOR
Alternative for UPDATE CURSOR [message #299306] Mon, 11 February 2008 06:21 Go to next message
khresmoi
Messages: 24
Registered: October 2007
Junior Member
Hi!

I have a cursor inside a stored procedure. I need to update every records the cursor fetches, so i used an update cursor. but while executing, it fails with the following error msg

" ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc."

as i'm using Distinct for a view in the cursor definition. I cannot remove the view/distinct from the cursor definition. Using a normal cursor is complicated as the table doesn't have a unique combination and it's tough to locate the records for update. Sad

Any suggestions...
Re: Alternative for UPDATE CURSOR [message #299310 is a reply to message #299306] Mon, 11 February 2008 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
need to update every records

Quote:
the table doesn't have a unique combination

So you don't have physical records and so you can't update them.

Quote:
it's tough to locate the records for update

But you have to do it. World is not always easy.

Regards
Michel
Re: Alternative for UPDATE CURSOR [message #299317 is a reply to message #299306] Mon, 11 February 2008 07:16 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hmm,
The below link will be useful


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15637744429336
Re: Alternative for UPDATE CURSOR [message #299324 is a reply to message #299317] Mon, 11 February 2008 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Useful maybe, but not for the current question.

Regards
Michel
Re: Alternative for UPDATE CURSOR [message #299340 is a reply to message #299310] Mon, 11 February 2008 09:42 Go to previous messageGo to next message
khresmoi
Messages: 24
Registered: October 2007
Junior Member
Michel Cadot wrote on Mon, 11 February 2008 18:09
Quote:
need to update every records

Quote:
the table doesn't have a unique combination

So you don't have physical records and so you can't update them.


i didn't get that point. Please tell me what do you mean by physical records. Did you mean that if there is no unique combination how am I selecting records? For that i'm joining with a view and select the records using RANK function.

[Updated on: Mon, 11 February 2008 10:28] by Moderator

Report message to a moderator

Re: Alternative for UPDATE CURSOR [message #299341 is a reply to message #299340] Mon, 11 February 2008 09:54 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
If you have a table that contains repeating values :
rowid, col1, col1
A1       A     B
A2       A     C
A3       A     B


And you do a SELECT DISTINCT col1, col2 ...

 col1, col1
   A     B
   A     C


What would be the rowid of A, B? You cannot identify it, because it could be one of any number (we can't differentiate how many) therefore you do not have a Physical row (i.e. you cannot identify the rowid of a row to update)

[Mod-edit: Frank modified misspelled code-tags]

[Updated on: Mon, 11 February 2008 10:45] by Moderator

Report message to a moderator

Previous Topic: SQL to find chained item groups
Next Topic: Force a Number to 2dp
Goto Forum:
  


Current Time: Sat Dec 10 13:07:12 CST 2016

Total time taken to generate the page: 0.04625 seconds