Error 1064 - SQL syntax in MySQL for an INSERT statement

Question

i am trying this query but didn't work.

ERROR: #1064 - 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_university FROM university WHERE university = "xxx") , `singl' at line 2

INSERT INTO `database_charts`.`university_has_single_user` (
        `university_id_university` = (SELECT id_university FROM university WHERE university = "xxx") , 
        `single_user_users_id_user` = (SELECT users_id_user FROM single_user WHERE users_id_user = "1" ), 
        `course_id_course`= (SELECT id_course FROM course WHERE course = "yyy"),
        `year`= "2000" )

How can this be solved?

1) What do you want to achieve? 2) What do you mean this "didn't work"? — Tadeck, Jun 13 '11 at 18:08
This isn't a question (http://www.catb.org/~esr/faqs/smart-questions.html). — marto, Jun 13 '11 at 18:09

score 3 · Accepted Answer · answered Jun 13 '11 at 18:15

Unless MySQL supports a very strange syntax, that's not how you write an INSERT statement. It takes this form:

INSERT INTO <TableName> (<Column1,Column2,..,ColumnN>) VALUES (<Value1,Value2,..,ValueN>)

So you would have

INSERT INTO database_charts.university_has_single_user (
  university_id_university, 
  single_user_users_id_user, 
  course_id_course,year) 
VALUES (
  (SELECT id_university FROM university WHERE university = 'xxx'),
  (SELECT users_id_user FROM single_user WHERE users_id_user = '1' ),
  (SELECT id_course FROM course WHERE course = 'yyy'),
  '2000'
)

Caveat: I'm not sure whether MySQL will actually handle a nested SELECT for each column value, but the above is how INSERT statements are typically written.

I think, this might throw an error if one of the SELECTs return more than one value. Good answer between, it didn't flash to me at all. — Praveen Lobo, Jun 14 '11 at 01:49

score 0 · Answer 2 · answered Jun 13 '11 at 18:14

0

INSERT INTO TABLE2 (TABLE2_FIELD)
  SELECT TABLE1_FIELD
  FROM TABLE1 
  WHERE TABLE1_SOMEFIELD IS 'SOMETHING'

More here http://dev.mysql.com/doc/refman/5.6/en/insert-select.html

answered Jun 13 '11 at 18:14

Praveen Lobo

6,956
2
28
40

This is same as @Tadeck's answer, I was typing it while Tadeck posted. Anyway, enjoy. – Praveen Lobo Jun 13 '11 at 18:16

Error 1064 - SQL syntax in MySQL for an INSERT statement

2 Answers2