Android SQLite how to delete with limit

Question

I want to delete only one row from my database table User.

I tried this, but I have no idea how to use SQLITE_ENABLE_UPDATE_DELETE_LIMIT.

Here is my code:

    SQLiteDatabase sqLiteDatabase = this.openOrCreateDatabase("User", MODE_PRIVATE, null);

        //sqLiteDatabase.execSQL("INSERT INTO user (name, age) VALUES ('jihyo', 10)");

        sqLiteDatabase.execSQL("DELETE FROM user WHERE name ='jihyo' LIMIT 1");
        Cursor c = sqLiteDatabase.rawQuery("SELECT * FROM user WHERE name = 'jihyo'", null);
        int nameIndex = c.getColumnIndex("name");
        int ageIndex = c.getColumnIndex("age");

        c.moveToFirst();
        do {
            Log.v("name", c.getString(nameIndex));
            Log.v("age", c.getString(ageIndex));
        } while (c.moveToNext());

Here is the error:

android.database.sqlite.SQLiteException: near "LIMIT": syntax error (code 1): , while compiling: DELETE FROM user WHERE name ='jihyo' LIMIT 1

use the unique ID of the data to be deleted to delete it from your table — Atish Agrawal, Jul 06 '17 at 08:36
You should always use an id to delete a person, it's safer and better — Jordy Dieltjens, Jul 06 '17 at 08:37

score 2 · Answer 1 · answered Jul 06 '17 at 09:05

2

You can't apply limit in delete query because DELETE Clause don't have LIMIT so you can Select single record using SELECT query and apply DELETE on the bases of selected query eg.

DELETE FROM user WHERE id IN
 (SELECT id FROM user
 WHERE name = 'jihyo'
 LIMIT 1);

Note: you can use '=' in place of 'IN'

answered Jul 06 '17 at 09:05

cpt. Sparrow

415
8
22

Thanks for answering my question. I added two data like name = 'jihyo' age = 10, name = 'lihyo', age = 10 and sqLiteDatabase.execSQL("DELETE FROM user WHERE name IN (SELECT name FROM user WHERE name ='jihyo' LIMIT 1)"); but the result is = it deleted all data It seemed limit wasn't work.... – JillAndMe Jul 07 '17 at 07:21
do you know why??? – JillAndMe Jul 07 '17 at 07:30
You have applied condition on basis of name so it select query fetch one record containing name='jihyo' so delete query become 'Delete from user where name in('jihyo') so it will delete all users whose name is 'jihyo', you need to use other column name which is unique like id or some other column. – cpt. Sparrow Jul 07 '17 at 08:52
I get it thank you for answering my question have a good day! – JillAndMe Jul 10 '17 at 05:33

Oussema Aroua · Accepted Answer · 2017-07-07T09:04:42.083

0

Try this:

db.execSQL("Delete from user where user_id IN (Select user_id from user where name = 'jihyo' limit 1)");

[UPDATED] : the = doesn't work with the LIMIT operation so you need to make it like this :

Cursor c = db.execSQL("Select user_id from user where name = 'jihyo' limit 1");
        if (c.moveToFirst()){
            String id = c.getString(0);
            db.execSQL("Delete from user where user_id = "+id);
        }

edited Jul 07 '17 at 09:04

answered Jul 06 '17 at 08:41

Oussema Aroua

5,225
1
24
44

Thanks for answering my question. I added two data like name = 'jihyo' age = 10, name = 'lihyo', age = 10 and sqLiteDatabase.execSQL("DELETE FROM user WHERE name IN (SELECT name FROM user WHERE name ='jihyo' LIMIT 1)"); but the result = it deleted all' It seemed limit wasn't work.... – JillAndMe Jul 07 '17 at 07:16
do you know why? – JillAndMe Jul 07 '17 at 07:30
i updated my responce – Oussema Aroua Jul 07 '17 at 09:04
thanks but did you use unique id? – JillAndMe Jul 10 '17 at 05:34
yes it's the `id` – Oussema Aroua Jul 10 '17 at 08:30

Android SQLite how to delete with limit

2 Answers2