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.
Printable View
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.
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.
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 ...;