所以这里的场景是,我在数据库中有4个表:
-
"question_info":
CREATE TABLE question_info ( q_id mediumint(9) NOT NULL, q_type_id int(11) NOT NULL, q_options_id mediumint(9) NOT NULL, q_category_id int(11) NOT NULL, q_text varchar(2048) NOT NULL, status tinyint(4) NOT NULL DEFAULT '0', q_date_added date NOT NULL DEFAULT '2013-01-01', q_difficulty_level tinyint(4) NOT NULL DEFAULT '0', PRIMARY KEY(q_id) );
-
"question_options_info":
CREATE TABLE question_options_info ( q_options_id mediumint(9) NOT NULL, q_options_1 varchar(255) NOT NULL, q_options_2 varchar(255) NOT NULL, q_options_3 varchar(255) NOT NULL, q_options_4 varchar(255) NOT NULL, q_options_ex_1 varchar(1024) DEFAULT NULL, q_options_ex_2 varchar(1024) DEFAULT NULL, q_options_ex_3 varchar(1024) DEFAULT NULL, q_options_ex_4 varchar(1024) DEFAULT NULL, PRIMARY KEY(q_options_id) );
-
"question_answer_info":
CREATE TABLE question_answer_info ( q_id mediumint(9) NOT NULL, q_options mediumint(9) NOT NULL );
-
"trivia_data":
CREATE TABLE trivia_data ( q_id mediumint(9) NOT NULL, q_text varchar(2048) NOT NULL, q_options_1 varchar(255) NOT NULL, q_options_2 varchar(255) NOT NULL, q_options_3 varchar(255) NOT NULL, q_options_4 varchar(255) NOT NULL, q_options mediumint(9) NOT NULL, q_difficulty_level tinyint(4) NOT NULL DEFAULT '0', q_date_added date NOT NULL DEFAULT '2015-04-8', PRIMARY KEY(q_id) );
所以我需要的是,将数据插入 trivia_data
表 . 此查询返回数据:
SELECT question_info.q_id, question_info.q_text, question_options_info.q_options_1, question_options_info.q_options_2, question_options_info.q_options_3, question_options_info.q_options_4, question_answer_info.q_options, question_info.q_difficulty_level, question_info.q_date_added FROM question_info JOIN question_options_info ON question_info.q_options_id = question_options_info.q_options_id JOIN question_answer_info ON question_info.q_id = question_answer_info.q_id;
此查询将返回如下数据:
我已经尝试过这个特定的查询来插入数据:INSERT INTO trivia_data VALUES(q_id, q_text, q_options_1, q_options_2, q_options_3, q_options_4, q_options, q_difficulty_level, q_date_added) SELECT question_info.q_id, question_info.q_text, question_options_info.q_options_1, question_options_info.q_options_2, question_options_info.q_options_3, question_options_info.q_options_4, question_answer_info.q_options, question_info.q_difficulty_level, question_info.q_date_added FROM question_info JOIN question_options_info on question_info.q_options_id = question_options_info.q_options_id JOIN question_answer_info on question_info.q_id = question_answer_info.q_id;
但它总是返回此错误:near "SELECT": syntax error:
老实说,我是SQL的新手 . 所以请尽可能简单地解释一下 . 任何帮助,将不胜感激 . 谢谢 .
3 回答
您不需要
VALUES
关键字,因为您从查询中选择:通常,如果要插入记录,则语法为
如果要插入结果,语法如下:
如您所见,在这种情况下没有
VALUES
关键字从SQL中删除
VALUES
,因为在这种情况下值来自SELECT .删除
VALUES
关键字 . 试试这个: