ORA-02014:cannot select FOR UPDATE when using ROWNUM

9am, morning ...


I’m drinking my coffee and doing my morning run through my emails …

As I’m doing this, I’m regretting I have ever bought anything online …

Few emails not being spam contain some useful stuff for me. A friend of mine emailed me asking if I could help him with his problem.

He had a specific SQL with FOR UPDATE clause which was throwing ORA-02014 at him. Here is his SQL

SELECT tab.*
FROM
  (SELECT tab.ROWID AS rid, tab.*
   FROM changes tab
   WHERE state IN ('N', 'E')
   ORDER BY company, id, change_date
   ) tab
WHERE ROWNUM <= 10
FOR UPDATE OF tab.state;

Full error message is as follows

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

*Cause:    An attempt was made to select FOR UPDATE from a view which had not been merged because the view used syntax (for example, DISTINCT or GROUP BY) preventing the merge, or because of initialization file parameter settings.

*Action:   Determine the reason the view will not merge and, if possible, make changes so that it can.

Table was basically sort of a FIFO stack and he always wanted to process only first 10 oldest rows ordered by given columns. Because he is a very careful fellow he wanted to lock the rows as he was selecting them, because after that there was some processing of the data before changing them.

After few dead ends SQL which did the trick looks as follows

SELECT *
FROM changes
WHERE rowid IN
  (SELECT rid
   FROM
    (SELECT ROWID AS rid
     FROM changes
     WHERE state IN ('N', 'E')
     ORDER BY company, id, change_date
    )
  WHERE ROWNUM <= 10
  )
FOR UPDATE OF state;

I hope this post will save you some time … if not today then maybe some other day :)