Update all records of a table according to a view result

41 Views Asked by At

I have a view like below

ITEMNO  INCOME  OUTGO
AA         10     2
BB         15     0
...
so on  

I'd like to update every record of the ITEMS table (item cards) according to above view's results.

I created a stored procedure like below

SET TERM ^ ;
ALTER PROCEDURE UPDATEITEMSQTY (
    ACTIVEYEAR VARCHAR(4) )

AS
declare variable ITMNO VARCHAR(20);
declare variable INCOME NUMERIC(18, 2);
declare variable OUTGO NUMERIC(18, 2);

begin 
  FOR SELECT IM.ITEMNO,
             COALESCE(SUM(IM.INCOME),0) INCOME,
             COALESCE(SUM(IM.OUTGO),0) OUTGO
  FROM ITEMMOVEMENTS IM /* View */
  WHERE  EXTRACT(YEAR FROM IM.TDATE )=:ACTIVEYEAR and           
  GROUP BY IM.ITEMNO
  INTO :ITMNO,:INCOME,:OUTGO  
    
  DO
  BEGIN   
    UPDATE ITEMS /* Item Cards Table */
    SET INCOME=:INCOME, OUTGO=:OUTGO,
        STOCK= :INCOME - :OUTGO
    WHERE ITEMNO=:ITMNO;
  END
 SUSPEND;
End
^
SET TERM ; ^

Above stored procedure doesn't correctly update some records.

0

There are 0 best solutions below