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

Question

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;
         }
        }

define primary key to the column you want to make unique..then it will not have duplicate values... — Meenal
– Meenal, Commented Oct 12, 2014 at 15:22
$add = query("INSERT INTO stocks (id , symbol , shares) VALUES(? ,? ,? )ON DUPLICATE KEY UPDATE shares = shares + VALUES(shares)",$_SESSION["id"],strtoupper($_POST["symbol"]),$_POST["shares"]); this is a simple MySQL query might be helpful to you. — Asis
– Asis, Commented Oct 12, 2014 at 15:27

Panther · Accepted Answer · 2014-10-12 15:49:41Z

10

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..

edited Oct 12, 2014 at 15:49

answered Oct 12, 2014 at 15:33

Panther

9,5183 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Panther Over a year ago

does your table has a primary key col??

Ravi Over a year ago

db.execSQL("CREATE TABLE " + TABLE_CATEGER +"(IDD INTEGER PRIMARY KEY," +" Category TEXT(100));");

Ravi Over a year ago

can i make Category column as a primary key

Panther Over a year ago

Well, i suggest you use my code only if u dont have a auto increment col as primary key and also nothing is dependent on the auto increment col. To know wats the problem, i suggest you figure it out urself, itll take quite a research to find it :-/

Tarun Tak · Accepted Answer · 2014-10-12 15:28:16Z

3

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.

answered Oct 12, 2014 at 15:28

Tarun Tak

4211 gold badge5 silver badges12 bronze badges

Comments

Ravi · Accepted Answer · 2014-10-12 16:01:01Z

0

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.

answered Oct 12, 2014 at 16:01

Ravi

951 gold badge2 silver badges9 bronze badges

Comments

Vijay · Accepted Answer · 2014-10-12 17:30:11Z

0

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)

edited Oct 12, 2014 at 17:30

answered Oct 12, 2014 at 15:25

Vijay

4188 silver badges19 bronze badges

Comments

asim · Accepted Answer · 2017-03-09 07:47:28Z

0

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.

edited Mar 9, 2017 at 7:47

user1752532

answered Oct 12, 2014 at 15:59

asim

13 bronze badges

1 Comment

Sachin Over a year ago

Not working, I have used the same query but its inserting duplicate data

saigopi.me · Accepted Answer · 2018-04-19 09:20:37Z

0

use insertWithOnConflict() method instead of insert()

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

answered Apr 19, 2018 at 9:20

saigopi.me

15.1k3 gold badges89 silver badges57 bronze badges

Collectives™ on Stack Overflow

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

6 Answers 6

4 Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related