July 12th, 2013, 09:40 PM
Inserting select query results from source table into derived table
My problem is populating my last two columns in the derived_daily table (which is derived from the source table, prices_daily.
sequence INT(11) NO PRI AUTO_INCREMENT
symbol VARCHAR(6) NO MUL
date DATE NO MUL
high DECIMAL(8,2) YES
low DECIMAL(8,2) YES
close_adj DECIMAL(8,2) YES
symbol VARCHAR(6) YES
date DATE NO
mov_avg10 DECIMAL(8,2) YES
std_dev10 DECIMAL(8,2) YES
range_daily DECIMAL(8,2) YES
range_std_dev30 DECIMAL(8,2) YES
I can populate the first 4 columns with the code below:
INSERT INTO derived_daily(symbol, date, mov_avg10, std_dev10) (SELECT t1.symbol, t1.date, AVG(t2.close_adj) AS mov_avg10, STDDEV(t2.close_adj) AS std_dev10 FROM prices_daily t1 LEFT OUTER JOIN prices_daily t2 ON t2.symbol = t1.symbol AND (t1.sequence - t2.sequence)BETWEEN 0 AND 9 WHERE t1.symbol = 'R' GROUP BY t1.date ) ;
but when I try to populate the 'range_daily' in the derived table with this:
INSERT INTO derived_daily(symbol, date, range_daily) (SELECT t2.symbol, t2.date, (high - low) AS range_daily FROM prices_daily t1 LEFT OUTER JOIN derived_daily t2 ON t2.symbol = t1.symbol AND t2.date = t1.date WHERE t2.symbol = 'C' ORDER BY t2.date ) ;
It places it in the correct column, but at the bottom of the table in new rows instead of the existing rows w/ the missing data (range_daily and range_std_dev30). I've tried a number of tweaks and am getting mostly "ERROR 1364, field 'date' doesn't have a default value. I want to populate the last 2 columns to match with what I've already placed in the table (same rows), not new ones at the bottom.
Any help/advice/etc. would be greatly appreciated! (Hopefully I did this right since it's my first Q on here).
July 12th, 2013, 09:59 PM
then you need an UPDATE, not an INSERT
Originally Posted by haddix
July 12th, 2013, 10:29 PM
First, thx for the reply. I've tried using UPDATE, but I'm unsure of the syntax since I need to JOIN the tables for 'range_daily'. I have thousands of rows for each stock and am confused as to how to use the UPDATE...SET...WHERE format in bulk form instead of one row at a time. Is it just a matter of changing the first line (INSERT INTO...) to UPDATE... and leaving the rest of the code the same?
Originally Posted by r937
Again, thanks for the help, it is much appreciated. Only been at this a few weeks!
July 13th, 2013, 04:13 AM
have a look at the manual, there's an example of a joined update
July 13th, 2013, 02:24 PM