How to execute a SQL query every second and insert output into another table?

user_14732606 0
Viewed 118

I am a designing a script to take some data and average it out. I have a MySQL database with two tables, Table 1 contains continuous data from sensor, it is stored as [id, data, timestamp]. Table has the format [id, hasAverage, timestamp].

What I want:

(1) is to every time take 6 values from table 1 and average them
(2) put the average into hasAverage in table 2

For part (1) I have made this SQL query which does the same:

SELECT AVG(data) FROM (SELECT data FROM Table1 ORDER BY id DESC LIMIT 0, 6) items;

When executed this gives average as output.

For Part (2), how can I put output from table 1 to table 2?

I am using MySQL in Xampp.

mysql sql
1 Answers

You could do an INSERT INTO <table> ... SELECT ..., called an insert-select query.

Example:

INSERT INTO table2 (average)
SELECT AVG(data) FROM (SELECT data FROM Table1 ORDER BY id DESC LIMIT 0, 6) items;

Simply replace average with whatever column name stores the value in your table2.

user_76440180
Related