 <p>Right; let's see what we have here.</p> <p>First, the code has to be blocked as follows:</p> <pre><code>variable declarations cursor declarations handler declarations everything else </code></pre> <p>So your <code>DECLARE CURSOR c2</code> <em>must</em> appear between <code>DECLARE CURSOR c1</code> and <code>DECLARE CONTINUE HANDLER</code>. Also, you only need one <code>CONTINUE HANDLER</code> because it takes effect from the point of declaration to the end of the procedure.</p> <p>Next is the statement</p> <pre><code>INSERT INTO ip_ER_subtotal SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, 0.0 FROM starting_pitchers_game_log; </code></pre> <p>The named columns in the <code>SELECT</code> clause are the columns you're <em>selecting from,</em> <strong>not</strong> the ones you're <em>inserting into,</em> so they have to be columns in the table <code>starting_pitchers_game_log</code>. Also, since the columns not being copied from <code>starting_pitchers_game_log</code> (that is, <code>ip_total</code>, <code>er_total</code> and <code>era</code>) all have default values, you could use a column list on the <code>INSERT</code> statement, like so:</p> <pre><code>INSERT INTO pitcher_stats_temp (Starting_Pitcher, Game_Date, Game_Number, innings_pitched, er) SELECT pitcher_id, game_date, game_seq, innings_pitched, runs FROM starting_pitchers_game_log; </code></pre> <p>This saves typing, documents which columns you're actually inserting values into and insulates your <code>INSERT</code> statement from the physical order of columns in the source and target tables.</p> <p>Next, once you finish the <code>CURSOR c1</code> loop, <strong>don't truncate the table</strong> or you'll lose all the work you've just done! <code>TRUNCATE TABLE</code> deletes all rows currently in the table, and is used here to clear out the results of the previous run.</p> <p>Finally, the two loops have to have different labels, say <code>fetch_loop_1</code> and <code>fetch_loop_2</code>. You would also need to reset <code>accum</code> and <code>end_of_cursor</code> before entering the second loop. However, in this case I believe we can do everything in one loop with one cursor, which makes the code simpler and thus easier to maintain.</p> <p>Here's the complete procedure:</p> <pre><code>DROP PROCEDURE IF EXISTS pitcher_stats_era; DELIMITER $$ CREATE PROCEDURE pitcher_stats_era() BEGIN DECLARE pit_id CHAR(10); DECLARE gdate DATE; DECLARE seq INT; DECLARE in_pit REAL; DECLARE er INT; DECLARE accum_ip REAL; DECLARE accum_er INT; DECLARE earned_run_avg REAL; DECLARE prev_year YEAR(4); DECLARE end_of_cursor BOOLEAN; DECLARE no_table CONDITION FOR SQLSTATE '42S02'; DECLARE c1 CURSOR FOR SELECT pitcher_id, game_date, game_seq, innings_pitched, earned_runs FROM pitcher_stats_temp ORDER BY pitcher_id, game_date, game_seq; DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_cursor := TRUE; DECLARE EXIT HANDLER FOR no_table BEGIN SIGNAL no_table SET MESSAGE_TEXT = "Work table not initialized. Please call pitcher_stats_reset() before continuing", MYSQL_ERRNO = 1146; END; ------------------------------------------------------------------ -- The following steps are now performed by pitcher_stats_reset() ------------------------------------------------------------------ -- TRUNCATE TABLE ip_subtotal; -- Clear our work table for a new run -- Copy data from main table into work table -- INSERT INTO ip_subtotal -- (pitcher_id, game_date, game_seq, innings_pitched, earned_runs) -- SELECT pitcher_id, game_date, game_seq, -- IFNULL(innings_pitched, 0), -- replace NULL with 0, if -- IFNULL(runs, 0) -- column not initialized -- FROM starting_pitchers_game_log; --------------------------------------------------------------------- SET end_of_cursor := FALSE; -- reset SET prev_year := 0; -- reset control-break OPEN c1; fetch_loop: LOOP FETCH c1 INTO pit_id, gdate, seq, in_pit, er; IF end_of_cursor THEN LEAVE fetch_loop; END IF; -- check control-break conditions IF YEAR(gdate) != prev_year THEN SET accum_ip := 0.0; SET accum_er := 0; SET prev_year := YEAR(gdate); END IF; SET accum_ip := accum_ip + in_pit; SET accum_er := accum_er + er; IF accum_er = 0 THEN -- prevent divide-by-zero SET earned_run_avg := 0; ELSE SET earned_run_avg := (accum_ip / accum_er) * 9; END IF; UPDATE pitcher_stats_temp SET ip_total = accum_ip, er_total = accum_er, std_era = earned_run_avg WHERE pitcher_id = pit_id AND game_date = gdate AND game_seq = seq; END LOOP; CLOSE c1; END $$ DELIMITER ; </code></pre> <p>That should do the job. If anyone finds a bug, by all means please point it out.</p> <p>EDIT: I've just added some code to illustrate how to protect against nulls coming from the source table, and how to avoid a divide-by-zero on the ERA calculation.</p> <p>EDIT: I've changed back to my original column and table names in order to reduce my own confusion.</p> <p>EDIT: Code changed to be consistent with the answer to <a href="http://stackoverflow.com/questions/33988958/how-can-i-add-a-column-to-a-work-table-using-a-new-stored-procedure">How can I add a column to a work table using a new stored procedure</a></p>