CODEWITHSUNDEEP
mysqlmysql-error-1064mysql-error-1136
Hogateer
Hogateer

Reputation: 1

MySQL Syntax Error in INSERT Subquery

I am working on inserting data into a secondary table, and it requires a sub-query to calculate and insert the rolling close values into the new table. When I try the query, I am getting one of the two errors:

  1. Either a column count does not match value count, or
  2. Syntax Error

I have validated that the destination table has all columns referenced in the insert statement. Any help would be appreciated. 

INSERT INTO `Historical`.`Secondary`(
`Record`,
    `Symbol`,
    `Date`,
    `Open`,
    `High`,
    `Low`,
    `Close`,
    `Volume`,
    `Rolling_Close`)

SELECT 
T1.Date, T1.Symbol, T1.Close,

(
SELECT AVG(T2.Close)

FROM Historical.Primary T2

WHERE T1.Symbol = T2.Symbol 

AND T2.Date BETWEEN DATE_SUB(T1.Date, INTERVAL 30 DAY) 

AND DATE_SUB(T1.Date, INTERVAL 1 DAY)) AS Rolling_Close

FROM Historical.Primary T1

GROUP BY T1.Date,

T1.Symbol;

The table Historical.Secondary has the following columns:

Record,Symbol,Date,Open,High,Low,Close,Volume,Rolling_Close

The table Historical.Primary has the following columns:

Record,Symbol,Date,Open,High,Low,Close,Volume

Upvotes: 0

Views: 235

Answers (1)

P.Salmon
P.Salmon

Reputation: 17590

Assuming you tables and data look similar to this

drop table if exists secondary;
create table secondary (record int auto_increment primary key,symbol int,dt date,open int, high int, low int,close int,volume int, rolling_close decimal(10,2));

drop table if exists prmary;
create table prmary (record int auto_increment primary key,symbol int,dt date,open int, high int, low int,close int,volume int);

alter table prmary
    add key k001(symbol,dt);

MariaDB [sandbox]> select * from prmary order by symbol,record;
+--------+--------+------------+------+------+------+-------+--------+
| record | symbol | dt         | open | high | low  | close | volume |
+--------+--------+------------+------+------+------+-------+--------+
|      1 |    707 | 2007-08-01 | NULL | NULL | NULL |   242 |   NULL |
|      3 |    707 | 2007-08-02 | NULL | NULL | NULL |     7 |   NULL |
|      5 |    707 | 2007-08-03 | NULL | NULL | NULL |     4 |   NULL |
|      7 |    707 | 2007-08-04 | NULL | NULL | NULL |     2 |   NULL |
|      2 |    712 | 2007-08-01 | NULL | NULL | NULL |   415 |   NULL |
|      4 |    712 | 2007-08-02 | NULL | NULL | NULL |     4 |   NULL |
|      6 |    712 | 2007-08-03 | NULL | NULL | NULL |     7 |   NULL |
|      8 |    712 | 2007-08-04 | NULL | NULL | NULL |     5 |   NULL |
+--------+--------+------------+------+------+------+-------+--------+
8 rows in set (0.00 sec)

This query 

truncate table secondary;
insert into secondary
(`Record`, `Symbol`, `Dt`,`Open`, `High`, `Low`,`Close`,`Volume`,`Rolling_Close`
)
SELECT record,T1.Symbol, T1.Dt,open, high, low,t1.close,volume,
(
SELECT avg(t2.close)
FROM Prmary T2
WHERE T1.Symbol = T2.Symbol 
AND T2.Dt BETWEEN DATE_SUB(T1.Dt, INTERVAL 30 day) 
AND DATE_SUB(T1.Dt, INTERVAL 0 day)
) AS Rolling_Close
FROM Prmary T1
GROUP BY t1.symbol,t1.dt;

produces this result

MariaDB [sandbox]> select * from secondary order by symbol, record;
+--------+--------+------------+------+------+------+-------+--------+---------------+
| record | symbol | dt         | open | high | low  | close | volume | rolling_close |
+--------+--------+------------+------+------+------+-------+--------+---------------+
|      1 |    707 | 2007-08-01 | NULL | NULL | NULL |   242 |   NULL |        242.00 |
|      3 |    707 | 2007-08-02 | NULL | NULL | NULL |     7 |   NULL |        124.50 |
|      5 |    707 | 2007-08-03 | NULL | NULL | NULL |     4 |   NULL |         84.33 |
|      7 |    707 | 2007-08-04 | NULL | NULL | NULL |     2 |   NULL |         63.75 |
|      2 |    712 | 2007-08-01 | NULL | NULL | NULL |   415 |   NULL |        415.00 |
|      4 |    712 | 2007-08-02 | NULL | NULL | NULL |     4 |   NULL |        209.50 |
|      6 |    712 | 2007-08-03 | NULL | NULL | NULL |     7 |   NULL |        142.00 |
|      8 |    712 | 2007-08-04 | NULL | NULL | NULL |     5 |   NULL |        107.75 |
+--------+--------+------------+------+------+------+-------+--------+---------------+
8 rows in set (0.00 sec)

Upvotes: 0

Related Questions