CODEWITHSUNDEEP
sqlfirebird
zac
zac

Reputation: 4908

Using update with cursor

I tried to use update inside For Select but I got error:

Invalid cursor reference. Relation mytable is not found in cursor TCUR.

Here is the procedure:

create or alter procedure MyPROC
returns (
    BIN_NO integer,
    QUANTITY integer)
as
declare variable V_BIN_TO integer;
declare variable V_BIN_FROM integer;
begin
  for
  select 
    mytable.bin_no,
    mytable.quantity
   from table2
   right outer join mytable on (table2.quote_id = mytable.quote_id)   
   into :bin_no, :quantity AS CURSOR tcur

  do begin
    v_bin_from = COALESCE(:v_bin_to,0) + 1;
    v_bin_to = COALESCE(:v_bin_to,0) + :quantity;

    update mytable set bin_no = v_bin_from || v_bin_to where current of tcur;

  end
end

How I should fix this ? This is Firebird database

Upvotes: 3

Views: 809

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109014

There are two separate problems. First: updatable cursors require the FOR UPDATE clause, otherwise it is read-only. Second: updatable cursors are only allowed to reference one table, so your query can't be updatable.

Upvotes: 2

Related Questions