SELECT .. FOR UPDATE gives error "cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc."

Question

I have the following FOR LOOP in an PLSQL Block . I want to use a FOR UPDATE clause to lock the respective row. But running the code gives following exception back:

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

I looked it up, but honestly I do not understand the explanations, so I can not solve that for now.

FOR l_rec IN (SELECT * 
                    FROM (SELECT * 
                            FROM my_table 
                           WHERE status = 'NEW'
                        ORDER BY key_id)
                   WHERE ROWNUM <= i_max
                   FOR UPDATE)
    LOOP
      -- do work...

      UPDATE my_table SET status = 'FINISHED' WHERE key_id = l_rec.key_id;

Could anybody give a comprehensive explanation of what is going on here and knwos how to solve that issue?

EDIR: Forgot to mention, my_table is no view, it is just a normal table.

The error message says it quite clearly: If the view uses DISTINCT, GROUP BY or similar, you cannot select FOR UPDATE. The reason is that, in such a view, there is no 1:1 relation from rows in the view to rows in an underlying table; hence, the RDBMS does not know which records it must lock on the underlying table and refuses to do so. — JimmyB, Sep 29 '17 at 10:05
possible duplicate of https://stackoverflow.com/questions/3166615/how-to-work-around-ora-02014-cannot-select-for-update-from-view-with-distinct — Kaushik Nayak, Sep 29 '17 at 10:07
Please read: [Update top N row of a select](https://asktom.oracle.com/pls/apex/asktom.search?tag=update-top-n-row-of-a-select) — Ponder Stibbons, Sep 29 '17 at 11:03

score 3 · Accepted Answer · answered Sep 29 '17 at 10:09

There can be couple of reason for the error you are getting. First one is already shown by Oracle.

1) You cannot use FOR UPDATE for views with DISTINCT, GROUP BY, etc. So make sure if your table my_table is not such a view.

2) Considering your table my_table is not a view, then when you use it in FOR LOOP you trying to create a inline view, hence in that case as well Oracle refuses to Lock rows. The work around could be as below:

Here i modifed the query so that it uses the table inplace of creating a inline view.

 FOR l_rec IN (   SELECT *
                  FROM my_table
                 WHERE     key_id IN (  SELECT key_id
                                          FROM my_table
                                         WHERE status = 'NEW'
                                      ORDER BY key_id)
                       AND ROWNUM <= i_max
                FOR UPDATE )
  LOOP

  <Do you work>

If you add another subquery inbetween the existing twos, your approach works, thanks! (1st level=FOR UPDATE, 2nd level= ROWNUM, 3rd Level = ORDER BY) — flo, Sep 29 '17 at 11:33

SELECT .. FOR UPDATE gives error "cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc."

1 Answers1