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.

Generally speaking, `LIMIT` without `ORDER BY` does not make sense. What is the logic determining _which_ single record gets updated? — Tim Biegeleisen, May 15 '20 at 02:20
@TimBiegeleisen have tried both, {ORDER BY LIMIT 1} and just {LIMIT 1}. They both don't work. — HashTables, May 15 '20 at 02:22
Please add sample data to your question which shows what you are trying to do here. I sense that the major problem is with SQL, not Java. — Tim Biegeleisen, May 15 '20 at 02:27
@TimBiegeleisen Ok, updated the title and post for more clarity. Thanks for the suggestion. — HashTables, May 15 '20 at 03:12
@HashTables: I'm just curious, what can this be useful for. Suppose you have 1 000 000 records and 1 000 of them fit the condition *where user is :name2*. If you limit your update, you will never know what record of these 1 000 records have you actually updated. And you will never know if there are other records that fit this condition and probably should also be updated. Can you tell us what sense does it make? — mentallurg, May 22 '20 at 00:49
You need to explain how/why you only want one record updated, otherwise nobody can help you. Do you want the first? the last? a random row? it just doesn't make sense... — solidau, May 28 '20 at 22:27

score 1 · Answer 1 · answered May 22 '20 at 13:12

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)

Sqlite-JDBC update with LIMIT clause?

1 Answers1