calculating simple moving average and insert into sql table

Question

I want to calculate a simple moving average and insert it into the table

Table (Kaffee) Columns are Datum (for date) and price and sma20 (which is empty and must be filled)

Datum	Price	sma20
1973-09-05	0.64	0.00
1973-09-06	0.66	0.00
1973-09-07	0.69	0.00

When I try this

Insert into kaffee(sma20)
select avg(price) OVER(
   ORDER BY datum ROWS BETWEEN 20 PRECEDING AND CURRENT ROW ) 
from kaffee;

It gives the following error:

#1364 - 'datum' doesn't have a default values

When is just start the select query its working fine.

Does anyone has a solution for this problem

Answer 1

Use an UPDATE statement in place of the INSERT one. You can exploit the "Datum" field to match the subquery and original table:

WITH cte AS (
    SELECT Datum,
           AVG(price) OVER(
               ORDER BY datum 
               ROWS BETWEEN 20 PRECEDING AND CURRENT ROW) AS average 
    FROM kaffee
)
UPDATE     kaffee
INNER JOIN cte
        ON kaffee.Datum = cte.Datum
SET sma20 = cte.average;

Check the demo here.