Is there any way to add the result column of SELECT to an already existing table in mySQL8?

I am trying to use the window function I’m mySQL to find the SMA for stock prices. I was able to get this done with a SELECT statement however the ALTER table statement doesn’t allow me to use the window function. My idea is to use the select statement to find the averages and concatenate the original table with the column it returns:

SELECT 
date_, close, AVG(close) OVER (ORDER BY date_ ASC ROWS 11 PRECEDING) AS SMA12 FROM intel_stock;

returns a table with 3 rows from the original table

Is there any way to “add/insert/join/unite” the newly calculated row to the right side of the original table (given that I have already added an extra empty column)?

Answer

You need to add the column first, then update it. The update ... join syntax comes handy for the second step.

-- add the new column
alter table intel_stock add column sma12 float; -- or whathever datatype is needed

-- set the new column
update intel_stock ist
inner join (
    select 
        date_, 
        avg(close) over (order by date_ rows 11 preceding) as sma12
    from intel_stock
) ist1 on ist1.date_ = ist.date_
set ist.sma12 = ist1.sma12

Note that it is probably simpler to use a view to store that derived information. This saves your the tedious task of maintaining the new column when the data changes in the surrounding rows:

create view intel_stock as
select 
    is.*, 
    avg(close) over (order by date_ rows 11 preceding) as sma12
from intel_stock