SELECT ... FOR UPDATE ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SELECT ... FOR UPDATE ?

  1. #1
    Join Date
    Mar 2001
    Posts
    109
    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

  2. #2
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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
  •  


Click Here to Expand Forum to Full Width