How to store the select result to a new column from the same table?
save query results as table sql server
sql update column with value from another column in the same table
insert data into table with result from another select query
select into temp table
use the results of a query as a field in another query
I am using DB2 of IBM. I have a table
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;
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;
- 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;
- 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");
Saving a Query Result in a Table, Saving a Query Result in a Table Problem You want to save the result from a the new rows are okay, copy them from the temporary table into your main table. columns from one table to another, you can shorten the statement to this form: To insert records into 'daysorder' table for the columns 'ord_date','ord_amount' and 'advance_amount' from the same columns of 'orders' table with following conditions - 1. the rows of 'orders' table should be arranged into a group according to 'ord_date', 2. the rows of 'orders' table should be arranged in descending order on 'ord_date' column,
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;
Inserting the result of a query in another table, The rows of another table will be fetched based on one or more criteria using To add records into 'daysorder' table for the columns 'ord_date'� You can transfer the results set from a select statement to a regular table from an into clause within a select statement. Insert the into clause after the select list and before the from clause. Name the regular table in the into clause. The table must reside in the current database context.
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.
Different ways to update rows in a table using SQL progressing, Learn how to update a column based on a filter of another column. depending on whether the current value of a column matches the condition, you can add a So to perform an update based on the value of another column in the same table, you of the statement if you want to set the column to a value in another table. First, specify a list of comma-separated columns from which you want to query the data in the SELECT clause. Then, specify the table name in the FROM clause. When evaluating the SELECT statement, the database system evaluates the FROM clause first and then the SELECT clause. The semicolon (;) is not the part of a query.
SQL Server SELECT, this tutorial introduces you to the basic of the SQL Server SELECT statement, focusing Database tables are objects that stores all the data in a database. Second, if the table is added one or more new columns, the SELECT * just retrieves� Insert Stored Procedure result into Temporary Table in SQL Example 1. In this SQL Server example, we will create a Temporary Table. Next, we are going to use the INSERT INTO SELECT Statement to insert the Stored Procedure result into Temporary Table
Combine Table Rows Using UNION, Unlike a join, which combines columns from different tables, a union combines rows from The number of columns must be the same for both select statements. If you want to keep all rows from both select statement's results use the ALL keyword. To do this you can add a new column indicating the category type: First, execute each SELECT statement individually. Second, combine result sets and remove duplicate rows to create the combined result set. Third, sort the combined result set by the column specified in the ORDER BY clause. In practice, we often use the UNION operator to combine data from different tables.
SQL INSERT INTO SELECT Statement, Into SQL Insert Into Select SQL Case SQL Null Functions SQL Stored Procedures SQL Comments The INSERT INTO SELECT statement copies data from one table and inserts it Copy only some columns from one table into another table: 2, New Orleans Cajun Delights, Shelley Burke, P.O. Box 78934, New Orleans� You can see I used column aliases to help make the query results easier to read. I also used a table alias, SOD, for the outer query. This makes it possible to use the outer query’s values in the subquery. Otherwise, the query isn’t correlated! Using the table aliases make it unambiguous which columns are from each table.
- Please could you give an example of the data in stockdb?
- Yes, i figured out the same solution as yours. Thanks
- thanks, but my main problem is i cannot store the ma into the original table
- @edwardluo - then calculate it on the INSERT.