1

I have an Syntax error in my SQL create table command but cant understand why?

CREATE TABLE E0 (
    div VARCHAR(50),
    date VARCHAR(50), hometeam VARCHAR(50), awayteam VARCHAR(50),
    fthg VARCHAR(50), ftag VARCHAR(50), ftr VARCHAR(50),
    hthg VARCHAR(50), htag VARCHAR(50), htr VARCHAR(50),
    referee VARCHAR(50), hs VARCHAR(50),
    as VARCHAR(50),
    hst VARCHAR(50), ast VARCHAR(50), hf VARCHAR(50),
    af VARCHAR(50), hc VARCHAR(50), ac VARCHAR(50),
    hy VARCHAR(50), ay VARCHAR(50), hr VARCHAR(50),
    ar VARCHAR(50), b365h VARCHAR(50), b365d VARCHAR(50),
    b365a VARCHAR(50), bwh VARCHAR(50), bwd VARCHAR(50),
    bwa VARCHAR(50), iwh VARCHAR(50), iwd VARCHAR(50),
    iwa VARCHAR(50), lbh VARCHAR(50), lbd VARCHAR(50),
    lba VARCHAR(50), psh VARCHAR(50), psd VARCHAR(50),
    psa VARCHAR(50), whh VARCHAR(50), whd VARCHAR(50),
    wha VARCHAR(50), vch VARCHAR(50), vcd VARCHAR(50),
    vca VARCHAR(50), bb1x2 VARCHAR(50), bbmxh VARCHAR(50),
    bbavh VARCHAR(50), bbmxd VARCHAR(50), bbavd VARCHAR(50), bbmxa VARCHAR(50), bbava VARCHAR(50), bbou VARCHAR(50), bbmx_2_5 VARCHAR(50), bbav_2_5 VARCHAR(50), bbmx_2_5 VARCHAR(50), bbav_2_5 VARCHAR(50), bbah VARCHAR(50), bbahh VARCHAR(50), bbmxahh VARCHAR(50), bbavahh VARCHAR(50), bbmxaha VARCHAR(50), bbavaha VARCHAR(50))
Dharman
  • 30,962
  • 25
  • 85
  • 135
emma perkins
  • 749
  • 1
  • 10
  • 28
  • At least in ANSI SQL `date` is a reserved word and needs to be delimited as `"date"`, or in the MySQL case using back-ticks. – jarlh Jan 21 '16 at 11:25

6 Answers6

4

as, div and date are sql reserved words (see manual), you cannot have such names without escaping with backticks.

Vasfed
  • 18,013
  • 10
  • 47
  • 53
2

One of the columns is named AS which is a reserved word. Either use different name or wrap around `` like as varchar(50)

Madhivanan
  • 13,470
  • 1
  • 24
  • 29
1

1)Div and AS are mysql reserved key it must be in backtick

2) Duplicate column bbmx_2_5 and bbav_2_5 remove

Whole query would be

CREATE TABLE E0 (`div` VARCHAR(50), date VARCHAR(50), hometeam VARCHAR(50), awayteam VARCHAR(50), fthg VARCHAR(50), ftag VARCHAR(50), ftr VARCHAR(50), hthg VARCHAR(50), htag VARCHAR(50), htr VARCHAR(50), referee VARCHAR(50), hs VARCHAR(50), `as` VARCHAR(50), hst VARCHAR(50), ast VARCHAR(50), hf VARCHAR(50), af VARCHAR(50), hc VARCHAR(50), ac VARCHAR(50), hy VARCHAR(50), ay VARCHAR(50), hr VARCHAR(50), ar VARCHAR(50), b365h VARCHAR(50), b365d VARCHAR(50), b365a VARCHAR(50), bwh VARCHAR(50), bwd VARCHAR(50), bwa VARCHAR(50), iwh VARCHAR(50), iwd VARCHAR(50), iwa VARCHAR(50), lbh VARCHAR(50), lbd VARCHAR(50), lba VARCHAR(50), psh VARCHAR(50), psd VARCHAR(50), psa VARCHAR(50), whh VARCHAR(50), whd VARCHAR(50), wha VARCHAR(50), vch VARCHAR(50), vcd VARCHAR(50), vca VARCHAR(50), bb1x2 VARCHAR(50), bbmxh VARCHAR(50), bbavh VARCHAR(50), bbmxd VARCHAR(50), bbavd VARCHAR(50), bbmxa VARCHAR(50), bbava VARCHAR(50), bbou VARCHAR(50), bbmx_2_5 VARCHAR(50), bbav_2_5 VARCHAR(50), bbah VARCHAR(50), bbahh VARCHAR(50), bbmxahh VARCHAR(50), bbavahh VARCHAR(50), bbmxaha VARCHAR(50), bbavaha VARCHAR(50))
Saty
  • 22,443
  • 7
  • 33
  • 51
0

One of your columns, as, is a reserved keyword

MichaelvdNet
  • 1,074
  • 2
  • 14
  • 22
0

Lot of issues:

  • DATE and DIV are keywords; use div and date with backticks `.
  • column name AS is not allowed after hs VARCHAR(50), reserved keyword
  • Duplicate columns bbmx_2_5 and bbav_2_5
Disha V.
  • 1,834
  • 1
  • 13
  • 21
0

MySQL has some keywords reserved.

We cannot use them as database/table/field name.

There are two ways to overcome this problem:

1) Adding a backtick.

2) Prepending parent name.

e.g. databasename.tablename

OR

table.fieldName

But, the best way is to avoid using reserved keywords.

If your field name is div, you can rename it to division, if

your field name is date, you can rename it as created_date.

Pupil
  • 23,834
  • 6
  • 44
  • 66