-
It seems that the query like SELECT DISTINCT oid FROM table1 FOR UPDATE does not work. I chcked the 9iSQL Reference and it didn't say you cannot use DISTINCT in SELECT ... FOR UPDATE.
Can any body confirm this. Thanks.
zm
-
Error: ORA 1786
Text: FOR UPDATE of this query expression is not allowed
-------------------------------------------------------------------------------
Cause: An attempt was made to use a FOR UPDATE clause on the result of a set
expression involving GROUP BY, DISTINCT, UNION, INTERSECT, or MINUS.
Action: Check the syntax, remove the FOR UPDATE clause, and retry the
statement.
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
If u going to use SELECT ... FOR UPDATE u have to undestand, that
Oracle implement TX (SHARED ROW) lock for each row in rowset.
Oracle use ROWID as pointer for row.
If u use DISTINCT that rows can't have ROWID as pointer of a row in a resultset.
because below, when u write:
UPDATE ... WHERE CURRENT OF ccc;
Oracle "convert" this statment to:
UPDATE ... WHERE rowid = ccc.rowid;
This is reason why u can't use DISTINCT with FOR UPDATE OF ...;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|