deleting one duplicate row in sqlite

Question

I wish to just delete one duplicate row in here (For example, Jim 21)

SQLiteDatabase myDataBase=this.openOrCreateDatabase("Users",MODE_PRIVATE,null);
myDataBase.execSQL("CREATE TABLE IF NOT EXISTS users (name VARCHAR,age INT(3))");
myDataBase.execSQL("INSERT INTO users(name,age) VALUES ('Rob', 34)");
myDataBase.execSQL("INSERT INTO users(name,age) VALUES ('Nat', 22)");
myDataBase.execSQL("INSERT INTO users(name,age) VALUES ('Jim', 21)");
myDataBase.execSQL("DELETE FROM users WHERE name='Jim'");
Cursor c=myDataBase.rawQuery(" SELECT * FROM users", null);

int nameIndex=c.getColumnIndex("name");
int ageIndex=c.getColumnIndex("age");

c.moveToFirst();
while (c!=null){
    Log.i("name",c.getString(nameIndex));
    Log.i("age",Integer.toString(c.getInt(ageIndex)));
    c.moveToNext();
}

I have tried this

myDataBase.execSQL("DELETE FROM users WHERE name='Jim' LIMIT 1");

But it is throwing a syntax error. I know LIMIT is not syntactically allowed in android. So how do I just delete one record of Jim when there are duplicates?

Thank you.

Possible duplicate of [Deleting duplicate rows from sqlite database](https://stackoverflow.com/questions/8190541/deleting-duplicate-rows-from-sqlite-database) — Ali, Mar 30 '18 at 06:51

score 0 · Accepted Answer · answered Mar 30 '18 at 06:40

0

Limit will not work with Delete query,it's only for Select number of record

Update the query

myDataBase.execSQL("DELETE FROM users WHERE name='Jim'");

you can add more condition for remove specific records

 myDataBase.execSQL("DELETE FROM users WHERE name='Jim' AND age=21 ");

answered Mar 30 '18 at 06:40

MJM

5,119
5
27
53

I did this but it's deleting all the values of Jim. Is there any other way to delete only one? Or should i add something like id ? – raviteja84 Mar 30 '18 at 06:43
yes you can add auto increment id in your table and using that you can delete the particular record – MJM Mar 30 '18 at 06:44

Reaz Murshed · Answer 2 · 2018-03-30T06:58:19.327

There are several ways to achieve this. However, I would suggest to put a unique constraint on your name field.

myDataBase.execSQL("CREATE TABLE IF NOT EXISTS users (name text unique not null, age INT(3))");

Now for creating new entries in your users table, get a function like the following.

public void createUser(List<User> userList) {
    if (userList != null && !userList.isEmpty()) {
        SQLiteDatabase db = this.openOrCreateDatabase("Users",MODE_PRIVATE,null);
        db.beginTransaction();

        try {
            for (User user : userList) {
                ContentValues values = new ContentValues();
                values.put("name", user.getName());
                values.put("age", user.getAge());

                // Replace on conflict with the unique constraint
                db.insertWithOnConflict("users", null, values, SQLiteDatabase.CONFLICT_REPLACE);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        db.setTransactionSuccessful();
        db.endTransaction();
    }
}

In this way, you do not have to delete any duplicate rows in your table as there will be no duplicate rows either.

However, if your implementation needs duplicate rows and then deleting only the first when you are trying to delete based on some condition then you might consider using the sqlite built-in column ROWID. You get all the rows that matches your condition and save the ROWID of them all. Then you delete the row that matches the ROWID you want to delete.

delete from users where ROWID = 9

Here's the developers documentation of using ROWID.

J. Hanney · Answer 3 · 2018-03-30T15:38:08.483

The approach I would take is to create a table where the duplicates are automatically resolved when data is inserted. Make the "name" field a primary key. Here's the CREATE statement:

CREATE TABLE users (name TEXT PRIMARY KEY ON CONFLICT IGNORE,age INTEGER);

"ON CONFLICT IGNORE" will always keep the first "name" record in the database. If you want to always keep the last record inserted, use "ON CONFLICT REPLACE". For example:

INSERT INTO users VALUES ('Jim','21');
INSERT INTO users VALUES ('Jim','23');
INSERT INTO users VALUES ('Jim','43');

If you use "ON CONFLICT IGNORE" Then "SELECT * FROM users" would produce "Jim|21". If you use "ON CONFLICT REPLACE" Then "SELECT * FROM users" would produce "Jim|43".

deleting one duplicate row in sqlite

3 Answers3