UcanaccessSQLException: unexpected token: ORDER

Question

I'm making a little app in Java and MySQL with PHPMyAdmin and all runs fine, but my professor says that we have to work with a database in Access, so I just changed my class connection and imported my database. The INSERT, SELECT and other UPDATE statements run fine but this statement just doesn't run.

UPDATE table SET col1=?, col2=? WHERE col0=? ORDER BY col4 DESC LIMIT 1

I can't understand how in MySQL it runs fine but with UCanAccess it doesn't work.

Here I don't understand what you trying to do. Are you trying to UPDATE or SELECT rows? — Eranda, May 08 '15 at 02:32
this sentence basically update one record but this record have to be the last record by this record, is one app for register the entries and exits of the employees of one office, the app create the record when the employee enter and update whe he leaves — Frozt360, May 08 '15 at 02:45
Please refer to the answer of http://stackoverflow.com/questions/9080403/update-with-order-by-and-limit-not-working-in-mysql which gives you an idea on what you want to do. — Eranda, May 08 '15 at 03:08
yes this code give me one idea, but this code is fo 2 tables and me code is for one table. I will be really thakfull if can give me one example.... — Frozt360, May 08 '15 at 03:36

score 3 · Answer 1 · answered May 10 '15 at 09:27

I can't understand how in MySQL it runs fine but with UCanAccess it doesn't work.

That's because the various producers of database software have taken it upon themselves to implement the SQL language in slightly different ways, so a given SQL statement written for MySQL is not guaranteed to work under Access, or Microsoft SQL Server, or Oracle, or any other "dialect" of SQL.

UCanAccess tries very hard to follow the Access SQL syntax. Access SQL uses TOP n instead of LIMIT n, but Access SQL also does not allow TOP n or ORDER BY in the main part of an UPDATE query. So you need to use a subquery to identify the primary key value of the row you want to update.

For example, if your table has a primary key column named "id" then you can do

sql = 
        "UPDATE table1 SET col1=?, col2=? " +
        "WHERE id IN ( " +
            "SELECT TOP 1 id " +
            "FROM table1 " +
            "WHERE col0=? " +
            "ORDER BY col4 DESC, id " +
        ")";

UcanaccessSQLException: unexpected token: ORDER

1 Answers1