[SOLVED] MySQL Syntax Error

worm5252 · 02-10-2010, 03:50 PM

Hey guys,
I am reading Sams Teach Yourself SQL in one hour a day. In this book they work with both Oracle and MySQL to teach you SQL. So I installed mysql on my box and I am creating the empty database they use in this book so I can follow along as I read. I have noticed a few typos in the book and now I have noticed some code errors too. I just don't know enough to fix em. I am working on creating the empty database and I am getting syntax errors when I create certain tables. I have checked both the printed version of the book and the electronic version (which differ slightly) and both give me the same synatax errors.

First is Creating the first table of the database. Here is the code given to me to enter.

The Electronic version

Quote:

create table checks
(‘ check’ integer not null,
payee varchar(20) not null,
amount decimal(6, 2) not null,
remarks varchar(20) not null) ;

The Printed Version

Quote:

create table checks
('check' integer not null,
payee varchar(20) not null,
amount decimal(6, 2) not null,
remarks varchar(20) not null);

In both cases I receive the following error

Code:

mysql> create table checks
    -> ('check' integer not null,
    -> payee varchar(20) not null,
    -> amount decimal(6,2) not null,
    -> remarks varchar(20) not null);
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 ''check' integer not null,
payee varchar(20) not null,
amount decimal(6,2) not n' at line 2

In addition to that I also have another table giving me a similar synatax error.

Quote:

create table convert
(name varchar(1 5) not null,
testnum numeric(9) not null) ;

Any ideas how I can correct this error and create these tables?

kbp · 02-10-2010, 05:08 PM

I don't do sql too often but I'll give it a shot -

- I don't think the single quotes are supposed to be there in the first one
- the second one has a gap between the two digits - '(1 5)' try removing it

You may be able to prevent future issues by downloading errata from the book website.. I haven't checked to see if this book has one but most do.

<edit>You may find it helpful to check the reference manual - http://dev.mysql.com/doc/index.html</edit>

cheers

worm5252 · 02-10-2010, 05:37 PM

Code:

mysql> create table checks (check integer not null, payee varchar(20) not null, amount decimal(6,2) not null, remarks varchar(20) not null);
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 'integer not null, payee varchar(20) not null, amount decimal(6,2) not null, rema' at line 1
mysql> create table convert
    -> (name varchar(15) not null,
    -> testnum numeric(9) not null);
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 'convert
(name varchar(15) not null,
testnum numeric(9) not null)' at line 1
mysql>

I believe check is a mysql command thus the need for single quotes. Also if I remove the gap in the second problem I still have an issue. I just don't get it.

Weird0ne · 02-10-2010, 06:04 PM

I believe if the field name is going to be the same name as a MySQL command it needs to be encapsulated in backticks `` not single quotes ''.

chrism01 · 02-10-2010, 09:00 PM

Indeed, but its best not to use names like that in the first place.
convert is also a keyword (actually a fn) http://dev.mysql.com/doc/refman/5.0/...functions.html.
That's a really(!) crap book. Use the docs on the website or you'll never get anywhere...
http://dev.mysql.com/doc/refman/5.0/en/index.html

worm5252 · 02-10-2010, 11:12 PM

Quote:

Originally Posted by Weird0ne

I believe if the field name is going to be the same name as a MySQL command it needs to be encapsulated in backticks `` not single quotes ''.

That is exactly what the problem was. Thanks. I would have never figured that out because I am still learning.