MYSQL INSERT INTO; Invalid Syntax

Question

If duplicate, please mark as I cannot otherwise find a solution.

I am executing a very simple SQL command that I have executed many other times in other environments but cannot find a rhyme or reason to this syntax error.

INSERT INTO tracking (range,purchase,trade_id) VALUES ("119.43-119.57","119.50","pid","961971");

I have tried this as well:

INSERT INTO tracking (range,pid,purchase,trade_id) VALUES ('119.43-119.57','119.50','pid','961971');

As well as not quoting numbers (trade_id).

I am receiving error:

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 'range,purchase,pid,trade_id) VALUES('119.43-119.57','119.50','pid' at line 1

My table structure:

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| trade_id | varchar(125) | NO   | PRI | NULL    |       |
| pid      | varchar(125) | NO   |     | NULL    |       |
| purchase | varchar(125) | NO   |     | NULL    |       |
| range    | varchar(125) | NO   |     | NULL    |       |
| sell     | varchar(5)   | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

And then my SQL version:

+-------------------------+
| VERSION()               |
+-------------------------+
| 5.5.60-0ubuntu0.14.04.1 |
+-------------------------+
1 row in set (0.00 sec)

I am also running on an Ubuntu 14.04 and running these commands from SQL CLI (Command Line Interface).

If anybody has a pointer of something I am blatantly missing, please show the way. I am going batty.

Thank you in advance.

Two things: (a) you have 3 fields but 4 values; that’s not going to work (b) Always use single quotes for strings: in MySQL default either is acceptable, but in ANSI mode as well as other databases double quotes would be an error. — Manngo, Jun 11 '18 at 04:41
Thank you! (a) That was an error in traversing from cli to stack, apologies and I will edit my original post to reflect such. (b) Thank you for the clarification on double quotes versus single quotes. — paxton91michael, Jun 11 '18 at 04:45
range is a keyword and must be escaped. Or bettet rename the column — Jens, Jun 11 '18 at 04:45
That actually maybe it, I should rename the column. Will let you know if that resolves. At which point I will accept answer if issue resolved. — paxton91michael, Jun 11 '18 at 04:46
Would you mind answering the question and I will accept? That resolved the issue. — paxton91michael, Jun 11 '18 at 04:52
Possible duplicate of [Using reserved words in column names](https://stackoverflow.com/questions/15725233/using-reserved-words-in-column-names) — Jens, Jun 19 '18 at 06:50

score 1 · Accepted Answer · answered Jun 11 '18 at 05:22

1

Documentation

Certain keywords, such as SELECT, DELETE, or BIGINT, are reserved and require special treatment for use as identifiers such as table and column names

The simplest solution is simply to avoid using reserved words as identifiers.

answered Jun 11 '18 at 05:22

munsifali

1,732
2
24
43

To clarify, "range" is a reserved term. Altering column name resolved the issue. Thanks a ton. – paxton91michael Jun 12 '18 at 01:03

score 1 · Answer 2 · answered Jun 11 '18 at 05:31

1

You can try this

INSERT INTO tracking (`range`,`pid`,`purchase`,`trade_id`) VALUES (`119.43-119.57`,`119.50`,`pid`,`961971`);

answered Jun 11 '18 at 05:31

Saurabh Ande

427
3
13

values can not be quoted with backticks. Ist must be quoted with single quotes – Jens Jun 11 '18 at 06:48
"range" is a reserved term per documentation and would need to be escaped or avoided. See Jens comment and deadman's (accepted) solution. – paxton91michael Jun 12 '18 at 01:02
1

I know range is a reserved key word,but in some case we accidentally use it and that becomes part of POC.Sounds bad but it's true.So backticks is there to help you as written in deadman's (accepted) solution. – Saurabh Ande Jun 12 '18 at 04:36

score 1 · Answer 3 · edited Jun 12 '18 at 14:18

1

Have you tried this out

INSERT INTO 'tracking' (range,pid,purchase,trade_id) VALUES ('119.43-119.57','119.50','pid','961971');

edited Jun 12 '18 at 14:18

Jens

67,715
15
98
113

answered Jun 11 '18 at 11:50

Krishna Devashish

76
6

Do not use single quotes around table names – Jens Jun 12 '18 at 14:18
@Jens can you please let me know why we have not to use quotes. – Krishna Devashish Jun 19 '18 at 06:45
Because single quotes encloses strings, not column or table names – Jens Jun 19 '18 at 06:47
Also see https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql/41442734 – Jens Jun 19 '18 at 06:48

score -1 · Answer 4 · answered Jun 11 '18 at 07:24

-1

Fixed query by suggestion from deadman:

INSERT INTO `tracking` (`range`, `purchase`, `pid`, `trade_id`) VALUES ("119.43-119.57", "119.50", "pid", "961971");

answered Jun 11 '18 at 07:24

Damian Dziaduch

2,107
1
15
16

MYSQL INSERT INTO; Invalid Syntax

4 Answers4