Insert with Select Query on Aggregate Function

Question

Referring to following thread: Can you use aggregate values within ON DUPLICATE KEY

I tried to copy that as well as many other ways to insert a value on ma20 but nothing has worked and I continually get the following error: Error Code: 1054. Unknown column 'histClose' in 'field list'

What am I doing wrong and how can I make this work?

insert into moving_average (ma_symbol, ma_date, ma20)
select 'A', max(histDate) as maxHistDate, avg(histClose) as histClose
from
   (select histDate, histClose
   from historical_data
   where symbol like 'A'
   order by histDate asc
   limit 20) temp
   on duplicate key update ma20 = values(histClose);

score 0 · Accepted Answer · answered Feb 21 '16 at 03:40

The values keyword takes the name of the column in the table . . . it is the value that would go into the column:

insert into moving_average (ma_symbol, ma_date, ma20)
    select 'A', max(histDate) as maxHistDate, avg(histClose) as histClose
    from (select histDate, histClose
          from historical_data
          where symbol like 'A'
          order by histDate asc
          limit 20
         ) temp
    on duplicate key update ma20 = values(ma20);

Insert with Select Query on Aggregate Function

1 Answers1