CODEWITHSUNDEEP
sqldb2
edwardluo
edwardluo

Reputation: 23

How to store the select result to a new column from the same table?

I am using DB2 of IBM. I have a table stockdb(date, close) I am calculating the 30 day moving average of it and store it into the same table.

alter stockdb 
add ma as avg("close") 
over
(order by "date" rows between 29 preceding and current row)
from stockdb;

Upvotes: 1

Views: 57

Answers (3)

user5085719
user5085719

Reputation:

Without example data; I am assuming you have 1 row per date and the close is maybe a closing retail value or something for a store?

Could you achieve what you want by doing this;

  1. Add the average column to your table - I've made it 9,2 decimal;
    ALTER TABLE STOCKDB                      
    ADD COLUMN MA DEC (9 , 2) NOT NULL WITH DEFAULT;
  1. Update your table, adding the average values to that column; 
UPDATE STOCKDB B                                        
   SET MA = (SELECT XMA                                         
               FROM (                                           
                SELECT "DATE",AVG("CLOSE")                      
                       OVER (ORDER BY "DATE" ROWS               
                       BETWEEN 29 PRECEDING AND CURRENT ROW) XMA
                FROM RAKELLR/STOCKDB) Z                         
              WHERE Z."DATE" = B."DATE")

So you build your date and averages within table "Z" - calling the average row XMA;

    SELECT "DATE",AVG("CLOSE")                      
           OVER (ORDER BY "DATE" ROWS               
           BETWEEN 29 PRECEDING AND CURRENT ROW) XMA
    FROM STOCKDB) Z;

Then you update you STOCKDB table setting the MA column to = XMA where the date matches;

UPDATE STOCKDB B                                        
   SET MA = (SELECT XMA                                         
               FROM (...Z...)                         
              WHERE Z."DATE" = B."DATE");

Upvotes: 0

Jim Castro
Jim Castro

Reputation: 894

INSERT INTO stockdb 
( date, 
  close, 
  ma
) 
VALUES 
( 
  CURRENT DATE,
  1.00,
  (select avg("close") 
          over (order by "date" rows between 29 preceding and current row ) 
    from stockdb )
)

The values are arbitrary, I could/should have used bind parms(?) just as easily. I noticed your table doesn't have symbol, open, high or low so these would have to be added if you alter the table.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271131

You cannot do this as a computed column because window functions are not allowed. Use a view instead:

create view v_stockdb as 
    select s.*,
           avg("close") over (order by "date" rows between 29 preceding and current row) as ma
    from stockdb s;

Upvotes: 1

Related Questions