I'm new to SQL and still learning.
Once I've done operations on a table (say table 1) to get a view. How do I translate/convert/store this view into a new permanent table (table 2)? Also, is it possible to store it in the same table (table 1)?
My code is something like this:
select `Date`, `Close Price`, round(avg(`Close Price`) over (order by `date` asc rows 19 preceding), 2) as '20 Day MA' from table1; -- this is my view
then i'd like table1 or a new table to store this view (the columns date, close price, 20 day ma)
Old table: date, close price
New table: date, close price, 20_day_ma
Maybe it's simpler to just add the new column 20_day_ma but I wouldn't know how to add the new values that rely on the other columns.
So just tried this:
DELIMITER $$ -- create table1 drop table if exists table1; create table table1 as select STR_TO_DATE(`Date`, '%d-%M-%Y') as `Date`, `Close Price`, round(avg(`Close Price`) over (order by `date` asc rows 19 preceding), 2) as '20 Day MA' from source_table $$ DELIMITER ;
And I constantly get the error: Commands out of sync