How to save query results column into a column in table in same database

Question

I'm having difficulty copying the results of this query (a column titled "FIPminus") into a table starting_pitcher_stats that I have in the same database.

When I run the following code, the results appear as in the screenshot beneath the code below. However, when I use an UPDATE statement below the ALTER TABLE statement, as discussed in the following thread, I keep getting the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 
IF(s.HomeAway_CD ='Home'

stackoverflow.com/questions/13317838/how-to-insert-a-query-result-into-a-column-mysql

ALTER TABLE starting_pitcher_stats ADD COLUMN FIPminus DOUBLE;
    SELECT 
    IF(s.HomeAway_CD ='Home'
    ,((((13*s.HR_a)+(3*(s.walks_a+s.HBP))-(2*s.strike_outs))/s.innings_pitched + g.cFIP + (((13*s.HR_a)+(3*(s.walks_a+s.HBP))-(2*s.strike_outs))/s.innings_pitched + g.cFIP - (((13*s.HR_a)+(3*(s.walks_a+s.HBP))-(2*s.strike_outs))/s.innings_pitched + g.cFIP * (p.Basic_PF/100))))/(b.FIP)*100)
    ,(((13*s.HR_a)+(3*(s.walks_a+s.HBP))-(2*s.strike_outs))/s.innings_pitched + g.cFIP/(b.FIP)*100)) AS FIPminus
    FROM starting_pitcher_game_log AS s
    INNER JOIN GUTS AS g
       ON s.YEAR_ID=g.YEAR_ID  
    INNER JOIN Park_Factors AS p
       ON s.YEAR_ID=p.YEAR_ID AND s.FLD_TEAM_ID=p.Team_ID
    INNER JOIN AL_NL_MLB_lg_avg_pitcher_stats AS b
       ON s.YEAR_ID=b.YEAR_ID AND s.lg_ID=b.lg_ID WHERE b.pitcher_type='starter'
    GROUP BY s.Starting_Pitcher, s.GAME_ID

Here is a screenshot of the structure of the table I'd like to copy into:

UPDATE: Here is the code that I used with the UPDATE statement that produced the error:

ALTER TABLE starting_pitcher_stats ADD COLUMN FIPminus DOUBLE;
UPDATE starting_pitcher_stats
SELECT 
IF(s.HomeAway_CD ='Home'
,((((13*s.HR_a)+(3*(s.walks_a+s.HBP))-(2*s.strike_outs))/s.innings_pitched + g.cFIP + (((13*s.HR_a)+(3*(s.walks_a+s.HBP))-(2*s.strike_outs))/s.innings_pitched + g.cFIP - (((13*s.HR_a)+(3*(s.walks_a+s.HBP))-(2*s.strike_outs))/s.innings_pitched + g.cFIP * (p.Basic_PF/100))))/(b.FIP)*100)
,(((13*s.HR_a)+(3*(s.walks_a+s.HBP))-(2*s.strike_outs))/s.innings_pitched + g.cFIP/(b.FIP)*100)) AS FIPminus
FROM starting_pitcher_game_log AS s
INNER JOIN GUTS AS g
   ON s.YEAR_ID=g.YEAR_ID  
INNER JOIN Park_Factors AS p
   ON s.YEAR_ID=p.YEAR_ID AND s.FLD_TEAM_ID=p.Team_ID
INNER JOIN AL_NL_MLB_lg_avg_pitcher_stats AS b
   ON s.YEAR_ID=b.YEAR_ID AND s.lg_ID=b.lg_ID WHERE b.pitcher_type='starter'
GROUP BY s.Starting_Pitcher, s.GAME_ID

Thank you in advance.

UPDATE: Here is the code that worked, but it took ridiculously long to run:

ALTER TABLE starting_pitcher_stats ADD COLUMN FIPminus DOUBLE;
UPDATE starting_pitcher_stats AS s
SET FIPminus = (SELECT f.FIPminus FROM FIPminus AS f
WHERE s.Starting_Pitcher=f.Starting_Pitcher AND s.GAME_ID=f.GAME_ID)

Please post full query you're trying to run the `UPDATE` with. — Kamil Gosciminski, Feb 19 '16 at 22:30
OK, I added the code with the UPDATE statement that produced the error to the original post...I also used INSERT INTO and that produced other types of errors. — LeeZee, Feb 20 '16 at 00:07
What are you trying to do? Insert new rows or update existing rows in `starting_pitcher_stats` table with value of `fipminus`? If you want to update existing rows, how do you want to "connect" your new information with existing row? There must be some common column both in your query and your destination table — Kamil Gosciminski, Feb 20 '16 at 09:01
Yes, I was trying to add a new column FIPminus to the table starting_pitcher_stats. This column should be a copy of the column FIPminus in the table FIPminus. I did come up with code that added the values, but it took a really long time. I'm thinking that this is because I used WHERE instead of INNER JOIN ON...AND to connect the new information with the existing rows. I added the code that worked to the original post. If I could do it with INNER JOIN instead, that would be ideal, but am having difficulty with that code. — LeeZee, Feb 21 '16 at 18:23
What is a common column between `starting_pitcher_stats` and your `select` query so that it knows for which row to update the `fipminus`? — Kamil Gosciminski, Feb 21 '16 at 18:25
Thanks Consider Me. The common columns between the two tables are Starting_Pitcher and GAME_ID. — LeeZee, Feb 21 '16 at 18:38

score 1 · Accepted Answer · answered Feb 21 '16 at 19:10

From what I understand you already populated your table FIPminus with values that you now need to update starting_pitcher_stats table with.

You can use JOIN syntax in UPDATE command.

UPDATE 
  starting_pitcher_stats AS s
  INNER JOIN FIPminus AS f ON 
    s.Starting_Pitcher = f.Starting_Pitcher AND s.GAME_ID = f.GAME_ID
SET FIPminus = f.FIPminus

If your query is running relatively slow for you, consider creating indexes (before running update !) which would include columns used in comparison on both tables:

ALTER TABLE FIPminus ADD INDEX (Starting_Pitcher, GAME_ID);
ALTER TABLE starting_pitcher_stats ADD INDEX (Starting_Pitcher, GAME_ID);

You could also read about speed of UPDATE statements and decide whether or not it is relevant for you.

Nice, Consider_Me. It worked well, instantaneously in fact, after indexing as you suggested. Thank you! — LeeZee, Feb 23 '16 at 03:42

How to save query results column into a column in table in same database

1 Answers1