Sqlite-JDBC update with LIMIT clause?

Question

I am trying to use the update query with the LIMIT clause using sqlite-JDBC.

Let's say there are 100 bob's in the table but I only want to update one of the records.

Sample code:

String name1 = "bob";
String name2 = "alice";

String updateSql = "update mytable set user = :name1 " + 
            "where user is :name2  " + 
            "limit 1";


try (Connection con = sql2o.open()) {
    con.createQuery(updateSql)
        .addParameter("bob", name1)
        .addParameter("alice", name2)
        .executeUpdate();
} catch(Exception e) {
    e.printStackTrace();
}

I get an error:

org.sql2o.Sql2oException: Error preparing statement - [SQLITE_ERROR] SQL error or missing database (near "limit": syntax error)

Using sqlite-jdbc 3.31

sql2o 1.6 (easy database query library)

The flag:

SQLITE_ENABLE_UPDATE_DELETE_LIMIT

needs to be set to get the limit clause to work with the update query.

I know the SELECT method works with the LIMIT clause but I would need 2 queries to do this task; SELECT then UPDATE.

If there is no way to get LIMIT to work with UPDATE then I will just use the slightly more messy method of having a query and sub query to get things to work.

Maybe there is a way to get sqlite-JDBC to use an external sqlite engine outside of the integrated one, which has been compiled with the flag set.

Any help appreciated.

Answer 1

You can try this query instead:

UPDATE mytable SET user = :name1
 WHERE ROWID = (SELECT MIN(ROWID) 
                  FROM mytable
                 WHERE user = :name2);

ROWID is a special column available in all tables (unless you use WITHOUT ROWID)