MySQL error 1064: SQL syntax error in SELECT statement

Question

I am relatively new to somewhat advanced MySQL querying. I had been trying to query the most recent order in an order table of a particular user using MySQL SELECT statement using the following MySQL query.

SELECT o1.* FROM order AS o1
WHERE o1.orderDateTime = 
( 
  SELECT MAX(o2.orderDateTime) FROM order AS o2
  WHERE o2.userId = '1'
)

But I had been constantly getting the following MySQL error #1064 related to MySQL syntax.

#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 'order AS o1 WHERE o1.orderDateTime = (SELECT MAX(o2.orderDateTime)FROM order AS ' at line 1

I got similar errors in relation with INSERT statements but I managed to fix it up using the methods specified in MySQL 1064: You have an error in your SQL syntax I made every effort to fix the query in the current case but I was still unsuccessful.

I would be grateful to you if someone can help me out with fixing this MySQL syntax error for SELECT clause specified above. It would be great if someone could specify me the exact reason for the occurrence of this issue, as well.

order is reserved word use bacticks to escape

M Khalid Junaid
– M Khalid Junaid

2015-05-06 05:54:59 +00:00
Commented May 6, 2015 at 5:54 — M Khalid Junaid
– M Khalid Junaid, Commented May 6, 2015 at 5:54

Tim Biegeleisen · Accepted Answer · 2015-05-06 05:59:17Z

6

order is a reserved word and its a bad choice for table name. You need to escape using backticks in the query

SELECT o1.* FROM `order` AS o1
WHERE o1.orderDateTime = (
    SELECT MAX(o2.orderDateTime) FROM `order` AS o2
    WHERE o2.userId = '1'
)

http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html

edited May 6, 2015 at 5:59

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

answered May 6, 2015 at 5:54

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

1 Comment

Chiranga Alwis Over a year ago

Yeah it worked for me... Understood the reason as well... Thanks a lot

Pupil · Accepted Answer · 2015-05-06 06:07:26Z

0

As per @Abhik, order is a MySQL keyword.

And you should avoid collapse with two methods:

Use backticks (`) (@Abhik has already explained this.)
Prepend Database name before Table Name e.g. DataBase_Name.order.

But, still @Abhik's approach is preferable as in case of database name change, you need to change DataBase name in your query.

answered May 6, 2015 at 6:07

Pupil

24k6 gold badges49 silver badges71 bronze badges

2 Comments

Sougata Bose Over a year ago

You are referring Abhik's answer for almost everything you mentioned. So it would be better to upvote the answer instead of adding a new answer.

Pupil Over a year ago

@sgtBose, thanks for the comment. I have already upvoted. But, I just wanted to mention that there is a second way also to fix the issue along with referred answer.

akr · Accepted Answer · 2015-05-06 06:14:49Z

0

First of all you could follow @Abhik Chakraborty suggestion to include back ticks around order table name. order is a reserved word in mysql. My suggestion was to improve your sql query. YOu could acomplish the same using:

SELECT o1.* FROM `order` o1
WHERE  o1.userId = '1' order by orderDateTime desc limit 1

the subquery seems unnecessary.

answered May 6, 2015 at 6:14

akr

7394 silver badges15 bronze badges

1 Comment

pala_ Over a year ago

note this is not much use if the intent is for this query to be further used in forming other queries

Collectives™ on Stack Overflow

MySQL error 1064: SQL syntax error in SELECT statement

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related