CODEWITHSUNDEEP
mysqlsyntax
user2884621
user2884621

Reputation: 151

SQL syntax error - SELECT statement within INSERT

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)";

Upvotes: 0

Views: 66

Answers (5)

Ashokreddy
Ashokreddy

Reputation: 352

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')";

Upvotes: 0

Puneet Kushwah
Puneet Kushwah

Reputation: 1600

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

Upvotes: 0

Z .
Z .

Reputation: 12837

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

Upvotes: 0

Saravana Kumar
Saravana Kumar

Reputation: 3729

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";

Upvotes: 0

Chris Farmer
Chris Farmer

Reputation: 25386

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

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

Upvotes: 2

Related Questions