#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Location
    Las Vegas
    Posts
    3
    Rep Power
    0

    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.

    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

    derived_daily:
    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).

    Thanks,
    Tom
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by haddix
    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.
    then you need an UPDATE, not an INSERT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Location
    Las Vegas
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    then you need an UPDATE, not an INSERT
    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?

    Again, thanks for the help, it is much appreciated. Only been at this a few weeks!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    have a look at the manual, there's an example of a joined update
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Location
    Las Vegas
    Posts
    3
    Rep Power
    0
    Thank you.

IMN logo majestic logo threadwatch logo seochat tools logo