For update of this query expression is not allowed (Cursor)

I am running below query

Select location, sum(units) as Units
from
( Select a.from_loc as location, sum(units) as units
from emp a, dept b
where a.id=b.id
Union all
Select a.to_loc as location, sum(units) as units
feom emp a, dept b
where a.id=b.id)
group by location;

Above query is giving me data in below format.

Location | sum(Units)
--------------------
100      |  350
200      |  450

Now i need to update another table Class with units given by above query. Class is having Location as primary key column and units column also.

I tried to create a cursor but its throwing error, for update cannot be used

Here is snippet of cursor code

Declare
V_location number(20);
V_units (20),
Cursor c1 is
Select location, sum(units) as Units
from
 ( Select a.from_loc as location, sum(units) as units
   from emp a, dept b
    where a.id=b.id
    Union all
    Select a.to_loc as location, sum(units) as units
     from emp a, dept b
     where a.id=b.id)
      group by location -----above select query
 for update;
Begin
 Open c1;
 Loop
 Fetch c1 into v_location, v_units;
 Exit when c1%notfound;

 Update class set units=v_units
 where location=v_location;
  End loop;
  Close c1;
 End;

Its throwing For update of this query expression is not allowed Could someone please let me know what i am doing wrong in here. Or some other approach to update the class table

1 answer

  • answered 2018-07-12 09:53 Littlefoot

    Would this do any good?

    • I presume that the FOR UPDATE clause causes problems
    • I removed the whole DECLARE section and used your SELECT statement in a cursor FOR loop as it is easier to maintain (no opening, closing, exiting, ...)

    BEGIN
       FOR cur_r IN (  SELECT location, SUM (units) AS units
                         FROM (SELECT a.from_loc AS location, SUM (units) AS units
                                 FROM emp a, dept b
                                WHERE a.id = b.id
                               UNION ALL
                               SELECT a.to_loc AS location, SUM (units) AS units
                                 FROM emp a, dept b
                                WHERE a.id = b.id)
                     GROUP BY location)
       LOOP
          UPDATE class
             SET units = cur_r.units
           WHERE location = cur_r.location;
       END LOOP;
    END;