SELECT ... FOR UPDATE ?

[mzhu]

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.

[ronnie]

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.

[Shestakov]

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