MySQL INSERT syntax giving a weird error

Question

I'm facing a very weird issue with a MySQL database. I'm trying to insert some values into a table that I've created but facing an error 1064. I've changed every single part of my syntax. Here's verbatim what I've been typing on my terminal:

mysql -u root -p
CREATE DATABASE robotics
USE robotics
CREATE TABLE `2017marea` (
    `Team` INT(25) NOT NULL, 
    `Match` INT(25) NOT NULL, 
    `Auto` INT(25) NOT NULL, 
    `Gear` INT(25) NOT NULL, 
    `Kpa` INT(25) NOT NULL, 
    `Climb` INT(25) NOT NULL
);
INSERT INTO 2017marea (Team, Match, Auto, Gear, Kpa, Climb) VALUES (1721, 1, 3, 5, 5, 1);

At this point, MySQL will return me an 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 'Match, Auto, Gear, Kpa, Climb) VALUES (1721, 1, 3, 5, 5, 1);

I know that this is a syntax error. However, changing my values syntax doesn't seem to affect the error at all, and furthermore nothing I do seems to be stopping the error. I've tried surrounding every value in every different type of quotes or ticks that I could think of, and the initial columns also. I'm confused, what's happening here?

Thanks.

Tim Biegeleisen · Accepted Answer · 2017-03-14T15:55:48.060

Match is a reserved keyword in MySQL. You can get around the error by placing it in backticks, e.g.

INSERT INTO 2017marea (Team, `Match`, Auto, Gear, Kpa, Climb)
VALUES (1721, 1, 3, 5, 5, 1);

I noticed that when your created your table, you placed every column name in backticks. This prevented you from seeing the error at that time. Had you done this instead:

CREATE TABLE `2017marea` (
    Team INT(25) NOT NULL, 
    Match INT(25) NOT NULL, 
    Auto INT(25) NOT NULL, 
    Gear INT(25) NOT NULL, 
    Kpa INT(25) NOT NULL, 
    Climb INT(25) NOT NULL
);

you would have hit the error before even creating the table.

In general you should avoid using MySQL reserved keywords for your database, table, and column names.

Thank you for your help! I didn't realize - I'm rather new to MySQL in the manner I'm using it. — Brennan Macaig, Mar 14 '17 at 15:54

score 2 · Answer 2 · answered Mar 14 '17 at 15:54

2

The word "Match" in the INSERT statement should be put in `` I think

answered Mar 14 '17 at 15:54

dfsg76

504
1
6
22

MySQL INSERT syntax giving a weird error

2 Answers2