CODEWITHSUNDEEP
androidsqlite
Ravi
Ravi

Reputation: 95

How to prevent to insert duplicate value in sqlite database (if duplicate then overwrite)

I had created two table in my database, In both table I am inserting value at the same time, now what I want to do is that, I want to insert record in second table, but the condition is that, if there is two same record then I want insert only one record not duplicate value, In second table there is two field one is id and second is category, when user insert two same category that time I want to insert only one entry, below is my code which is not working properly, It insert all record accept duplicate value..

public long InsertCat(String idd, String cat) 
  { 
     try 
       {
        SQLiteDatabase db;
        long rows = 0;
        db = this.getWritableDatabase(); 
        ContentValues Val = new ContentValues();
        Val.put("IDD", idd); 
        Val.put("Category", cat);
        Cursor c = db.rawQuery("SELECT * FROM " + TABLE_CATEGER + " WHERE Category='"+cat+"'",null);
        while(c.moveToNext())
        {
            if(c.getString(0).equals(cat))
            {
                flag=true;
            }
        }           
        if(flag==true)
        {
          rows=db.update(TABLE_CATEGER, Val, "Category='"+cat+"'"  , null);     
          System.out.print(rows);
          db.close();
        }
        if(flag==false)
        {
            rows = db.insert(TABLE_CATEGER, null, Val);
            System.out.print(rows);             
            db.close();
        }
        return rows; // return rows inserted.
         } catch (Exception e) {
         return -1;
         }
        }

Upvotes: 6

Views: 29590

Answers (6)

saigopi.me
saigopi.me

Reputation: 14918

use insertWithOnConflict() method instead of insert()

response = sqLiteDatabase.insertWithOnConflict(TABLE_TRACKER_LOGS_LINES, null, contentValues,SQLiteDatabase.CONFLICT_REPLACE);

Upvotes: 0

asim
asim

Reputation: 1

Use the following query

INSERT OR REPLACE INTO table_name (idColoumn, categoryColumn) VALUES (?, ?)

It will add new row if it does not exist or update row if it exists. hope this will help you.

Upvotes: 0

Vijay
Vijay

Reputation: 418

Check with the primary key of which you want assign then while inserting check with on duplicate key.. if you want you update the row

on duplicate key update

As mentioned i am writing the code on duplicate key

INSERT OR REPLACE INTO TABLE_CATEGER(value1,value2,value3)

Upvotes: 0

Ravi
Ravi

Reputation: 95

This is working for me,and i also created Category as a primary key..

ContentValues Val = new ContentValues();
Val.put("IDD", idd); 
Val.put("Category", cat);
long rows=db.insertWithOnConflict(TABLE_CATEGER, null,  Val,SQLiteDatabase.CONFLICT_REPLACE);
System.out.print(rows);
Log.d("kkkkkkkkkk",""+ rows);
db.close();
return rows; // return rows inserted.

Upvotes: 0

Panther
Panther

Reputation: 9408

Put all your values inside ContentValues and then call this on writableDatabase

db.insertWithOnConflict(tableName, null, contentValues,SQLiteDatabase.CONFLICT_REPLACE);

EDIT:

Well all you need is only this part of code

    SQLiteDatabase db;
    long rows = 0;
    db = this.getWritableDatabase(); 
    ContentValues Val = new ContentValues();
    Val.put("IDD", idd); 
    Val.put("Category", cat);
    rows = db.insertWithOnConflict(tableName, null, contentValues,SQLiteDatabase.CONFLICT_REPLACE);

insertWithOnConflict methods follows the last parameter as the reaction algorithm when an duplicate row is Found. And for a duplicate row to be found, the primary keys has to clash. If all this satisfys the row would surely get Replaced :-/ If not something else is going wrong..

Upvotes: 10

Tarun Tak
Tarun Tak

Reputation: 421

While creating your table, put constraint on your column(Primary key or Unique key).This will not only the duplicate value to be inserted into your database.

Upvotes: 3

Related Questions