Android Sqlite update first row only

Question

I need to update only the first row of a specific sql query command

my code is:

SQLiteDatabase db = this.getWritableDatabase();
    String strSQL = "UPDATE table SET column = something WHERE  name = john AND age = 10";
    db.execSQL(strSQL);

I have like 5 results with the same criteria of name=john and age of 10

this works to update the last item however I want it to update the first row result only of the WHERE clause. how can this be possible?

better use a unique column to identify your users, and for updating the first item `cursor.moveToFirst();` should work — Pemba Tamang, Sep 25 '19 at 09:04
I already have unique id for each user, but I'm just providing an example in my question. Isn't there a method to update the row from a single line instead of creating a cursor and updating the first one? — Mo Dev, Sep 25 '19 at 09:06
do not write a raw query, find the item using limit and then use `yourdb.update(tablename,contentvalues,where,whereargs[])` to update like this https://stackoverflow.com/a/9798527/8528047 — Pemba Tamang, Sep 25 '19 at 09:11
Find the id of the *first* record (What determines order?) and update just that one.... `UPDATE yourtable SET yourcolumn = whatever WHERE id = the_id_you_care_about`. — Shawn, Sep 25 '19 at 09:13
try "UPDATE table SET column = something WHERE name = john AND age = 10 ORDER BY unique_column DESC" — L2_Paver, Sep 25 '19 at 09:22

Rahul Khurana · Accepted Answer · 2019-09-25T09:13:39.143

1

try this:

String strSQL = "UPDATE table SET column = something WHERE id IN (SELECT id FROM table WHERE name = john AND age = 10 LIMIT 1)";

edited Sep 25 '19 at 09:13

answered Sep 25 '19 at 09:06

Rahul Khurana

8,577
7
33
60

android.database.sqlite.SQLiteException: near "LIMIT": syntax error (code 1): – Mo Dev Sep 25 '19 at 09:07
1

Using `LIMIT` with `UPDATE` in sqlite requires creating a custom version of the sqlite3.c amalgamation (Passing `--enable-update-limit` to the configure script in the sqlite3-src-XXXXXX.zip distribution), and compiling that with a specific option turned on (`SQLITE_ENABLE_UPDATE_DELETE_LIMIT`), btw. Probably not an option given android. – Shawn Sep 25 '19 at 09:07
@MoDev I've modified my answer – Rahul Khurana Sep 25 '19 at 09:14
do not write a raw query, find the item using limit and then use `yourdb.update(tablename,contentvalues,where,whereargs[])` to update like this https://stackoverflow.com/a/9798527/8528047 – Pemba Tamang Sep 25 '19 at 09:14
@MoDev Did you check? – Rahul Khurana Sep 25 '19 at 09:23
1

I believe it works! however I had another issue which prevented the change to be applied. Thanks for the solution. – Mo Dev Sep 27 '19 at 06:02

score 0 · Answer 2 · answered Sep 25 '19 at 09:12

0

Provided id is a PK, try

UPDATE table 
SET column = something 
WHERE id = (SELECT min(t2.id) 
            FROM table t2 
            WHERE t2.name = 'john' AND t2.age = 10)

answered Sep 25 '19 at 09:12

Serg

22,285
5
21
48

score 0 · Answer 3 · answered Sep 25 '19 at 10:10

First: (in your case) If you want to update only first row then add one more parameter in your table. It will help you to find that record uniquely

Second: Don't use update query like this

    String strSQL = "UPDATE table SET column = something WHERE  name = john AND age = 10";

do it like this instead

Android Sqlite update first row only

3 Answers3