SQL syntax error - SELECT statement within INSERT

Question

I have this line of sql which gives me a syntax error following the closing parenthesis of the select statement. I was under the belief that I could write the code this way. Is there some alternative?

The variables inside the select statement come from GET and the others come from POST. Any help is appreciated.

$sql2="INSERT INTO playerRegSeason 
       (playerID, year, teamID, gp, minutes, pts, oreb, dreb, reb, 
        asts, stl, blk, turnover, pf, fga, fgm, fta, ftm, tpa, tpm)
VALUES ((
       SELECT playerID FROM players WHERE firstname='$firstname' 
       AND lastname='$lastname' AND firstseason=$firstseason), $year, 
      '$team', $gp, $minutes, $pts, $oreb, $dreb, $reb, $asts, $stl, 
       $blk, $turnover, $pf, $fga, $fgm, $fta, $ftm, $tpa, $tpm)";

Chris Farmer · Accepted Answer · 2014-12-09 04:48:33Z

2

Don't use VALUES when you're inserting from a select.

INSERT INTO abc (foo, bar)
SELECT x, y FROM z

answered Dec 9, 2014 at 4:48

Chris Farmer

25.5k37 gold badges125 silver badges170 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Saravana Kumar · Accepted Answer · 2014-12-09 04:49:29Z

0

Use this.

$sql2="INSERT INTO playerRegSeason 
       (playerID, year, teamID, gp, minutes, pts, oreb, dreb, reb, 
        asts, stl, blk, turnover, pf, fga, fgm, fta, ftm, tpa, tpm)

       SELECT playerID, $year, '$team', $gp, $minutes, $pts, $oreb, 
         $dreb, $reb, $asts, $stl, $blk, $turnover, $pf, $fga, $fgm, 
         $fta, $ftm, $tpa, $tpm FROM players WHERE firstname='$firstname' 
       AND lastname='$lastname' AND firstseason=$firstseason";

answered Dec 9, 2014 at 4:49

Saravana Kumar

3,7295 gold badges19 silver badges39 bronze badges

2 Comments

user2884621 Over a year ago

I get this error: 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 playerID FROM players WHERE firstname='' AND lastname='' AND firstseason=' at line 2

Saravana Kumar Over a year ago

@user2884641: check the sample in sql fiddler sqlfiddle.com/#!2/f4ef5d/1 and sqlfiddle.com/#!2/ab89e/1

Z . · Accepted Answer · 2014-12-09 04:50:23Z

0

insert into table1 (field1, field2, ...)
select col1, col2, ...
from table2
where ....

answered Dec 9, 2014 at 4:50

Z .

12.8k2 gold badges34 silver badges59 bronze badges

Comments

Puneet Kushwah · Accepted Answer · 2014-12-09 04:52:01Z

0

The SQL statement you wrote is incorrect, you need to remove the values clause or you can refer Insert with select to learn how to write a insert with a select statement

answered Dec 9, 2014 at 4:52

Puneet Kushwah

1,6002 gold badges19 silver badges35 bronze badges

Comments

Ashokreddy · Accepted Answer · 2014-12-09 05:29:08Z

0

Use this query instead of that one it's working

$sql2="INSERT INTO playerRegSeason (playerID, year, teamID, gp ,minutes, pts, oreb, dreb, reb, asts, stl, blk, turnover, pf, fga, fgm, fta, ftm, tpa, tpm)

    values(SELECT playerID,year, $team, $gp, $minutes, $pts, $oreb, $dreb, 
    $reb, $asts, $stl, $blk, $turnover, $pf, $fga, $fgm, $fta, $ftm, $tpa, $tpm
    WHERE firstname='$firstname' 
    AND lastname='$lastname' AND firstseason='$firstseason')";

answered Dec 9, 2014 at 5:29

Ashokreddy

3521 silver badge11 bronze badges

Collectives™ on Stack Overflow

SQL syntax error - SELECT statement within INSERT

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related