Search code examples
mysqlselectmyisaminsert-into

INSERT INTO using data from other table causing ERROR 1064

My command for INSERT INTO was:

INSERT INTO environment_fid (FID_environment) VALUES SELECT ID FROM environment WHERE name='planes';

But I got:

ERROR 1064 (42000): 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 ID FROM environment WHERE name='planes'' at line 1

I have three tables, but I was tried with only two:

CREATE TABLE `environment_fid` (
  `ID` mediumint(9) unsigned AUTO_INCREMENT,
  `FID_environment` mediumint(9) unsigned,
  `FID_monster` mediumint(9) unsigned ,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM;

DROP TABLE IF EXISTS `environment`;
CREATE TABLE `environment` (
  `ID` mediumint(9) unsigned AUTO_INCREMENT,
  `name` text NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM;

However, when I put in parts:

INSERT INTO environment_fid (FID_environment) VALUES (78);

Query OK, 1 row affected (0.00 sec)

and

 SELECT ID FROM environment WHERE name='planes';

*+----+
| ID |
+----+
| 66 |
+----+

1 row in set (0.00 sec)*

I have no idea about this problem, any suggestion?

Thanks,

INSERT INTO environment_fid (FID_environment) SELECT ID FROM environment WHERE name='planes';

Solve my problem, but how can I get others tables? For example:

INSERT INTO environment_fid (FID_environment, FID_monster) 
SELECT ID FROM environment  WHERE name='planes',
SELECT ID FROM monster WHERE family='Blue';
Solution

  • remove values and change your query to :

    INSERT INTO environment_fid (FID_environment) 
SELECT ID FROM environment WHERE name='planes';

    or

    INSERT INTO environment_fid (FID_environment) VALUES (environment_id)