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
– Manngo, Commented Jun 11, 2018 at 4: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
– paxton91michael, Commented Jun 11, 2018 at 4:45
range is a keyword and must be escaped. Or bettet rename the column — Jens
– Jens, Commented Jun 11, 2018 at 4: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
– paxton91michael, Commented Jun 11, 2018 at 4:46
Would you mind answering the question and I will accept? That resolved the issue. — paxton91michael
– paxton91michael, Commented Jun 11, 2018 at 4:52

munsifali · Accepted Answer · 2018-06-11 05:22:55Z

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, 2018 at 5:22

munsifali

1,7432 gold badges24 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

paxton91michael Over a year ago

To clarify, "range" is a reserved term. Altering column name resolved the issue. Thanks a ton.

Saurabh Ande · Accepted Answer · 2018-06-11 05:31:07Z

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, 2018 at 5:31

Saurabh Ande

4273 silver badges13 bronze badges

3 Comments

Jens Over a year ago

values can not be quoted with backticks. Ist must be quoted with single quotes

paxton91michael Over a year ago

"range" is a reserved term per documentation and would need to be escaped or avoided. See Jens comment and deadman's (accepted) solution.

Saurabh Ande Over a year ago

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.

Jens · Accepted Answer · 2018-06-12 14:18:42Z

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, 2018 at 14:18

Jens

69.6k15 gold badges107 silver badges133 bronze badges

answered Jun 11, 2018 at 11:50

Krishna Devashish

866 bronze badges

4 Comments

Jens Over a year ago

Do not use single quotes around table names

Krishna Devashish Over a year ago

@Jens can you please let me know why we have not to use quotes.

Jens Over a year ago

Because single quotes encloses strings, not column or table names

Jens Over a year ago

Also see stackoverflow.com/questions/11321491/…

Damian Dziaduch · Accepted Answer · 2018-06-11 07:24:44Z

-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, 2018 at 7:24

Damian Dziaduch

2,1231 gold badge15 silver badges16 bronze badges

Collectives™ on Stack Overflow

MYSQL INSERT INTO; Invalid Syntax

4 Answers 4

1 Comment

3 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related