1

I can't figure out what's causing my INSERT INTO's to fail to certain table in MySql. I can manage them to other tables. The table looks like:

CREATE TABLE IF NOT EXISTS `Match` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `match_no` int(11) NOT NULL,
  `season` int(11) NOT NULL,
  `hometeam` int(11) NOT NULL,
  `awayteam` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `match_no` (`match_no`),
  KEY `season` (`season`),
  KEY `hometeam` (`hometeam`),
  KEY `awayteam` (`awayteam`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

And the command is 

INSERT INTO Match (`match_no`, `season`, `hometeam`, `awaytem`) VALUES (1, 1, 2, 3)

All I get is:

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 'Match (match_no, season, hometeam, awaytem) VALUES (1, 1, 2, 3)' at line 1

I have checked the manual and half-a-dozen examples from the web and whatnought and tried all sorts of changes to the syntax in case there is some MySql specific oddity, but nothing seems to work.

codaddict
  • 445,704
  • 82
  • 492
  • 529
Makis
  • 12,468
  • 10
  • 62
  • 71

3 Answers3

5

Change awaytem to awayteam and see how it goes and use `Match` as the table: match is a reserved word.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • Ah, the awaytem was just a typo I did when I quickly wrote the example, but the other hint was the key! So thank's a lot, can't believe how much time I wasted on this... – Makis Apr 18 '10 at 15:32
  • As a side note: While you *can* use a reserved word as a table/column name in MySQL (and other DMBS), I'd strongly recommend against it. It will be a source of trouble in the future, and not all tools may support the necessary tricks (such as using quotes). Just use a different table name, if you can. – sleske Apr 18 '10 at 17:30
  • Yeah, I don't usually use them, but this is just a small app that once I get ready will probably never touch again. – Makis Apr 19 '10 at 05:39
4

Match is a reserved word in MySQL.

Here goes the list of MySQL reserved words

Enclose Match in back ticks as:

INSERT INTO `Match` .........

Also as Pax pointed out you've misspelt a column name.

codaddict
  • 445,704
  • 82
  • 492
  • 529
2

Match is a reversed word
so,

INSERT INTO `Match`

note the same backticks you used for the fieldnames
these are not for decoration

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345