Why does this SQL INSERT statement return a syntax error?

Question

Ok I have a very simple mysql database but when i try to run this query via mysql-admin i get weird errors

INSERT INTO customreports (study, type, mode, select, description) VALUES ('1', '2', '3', '4', '5');

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, description) VALUES ('1', '2', '3', '4', '5')' at line 1

Answer 1

You're having problems because you're using SQL reserved words as column names and not escaping them. Try like this:

INSERT INTO `customreports`
(`study`, `type`, `mode`, `select`, `description`)
VALUES
('1', '2', '3', '4', '5');

Answer 2

just a guess, but is reserved word "select" (listed as a column name) causing a problem?

Answer 3

"SELECT" is a reserved word in SQL... use a different word than "select"

Answer 4

The word 'select' is reserved in sql. The interpreter thinks you're trying to use a SELECT statement inside the INSERT INTO statement. You need to enclose the field names so that the interpreter doesn't read them as commands.

Update for MySQL:

insert into customreports ('study','type','mode','select','description') values...

Answer 5

Yeah, I would rewrite as:

INSERT INTO [customreports] ([study], [type], [mode], [select], [description]) VALUES ('1', '2', '3', '4', '5');

Answer 6

Correct per Chaos... but the critical thing to remember is you should do your best to NOT use RESERVED words as column names in a table... such as SELECT and TYPE.... not positive of the other three, but that's where your conflict is. Yes, by being explicit with quotes around the fields will tell the SQL you mean the field within, but its also best to know WHY its failing, and not just a work-around resolution... helps prevent similar issues in the future.

Answer 7

Ditto the above, but I believe you need double quotes around the column names, not single quotes. Perhaps some flavors of SQL will process the single quotes correctly, but I think the standard says double quotes for field names, single quotes for field values.