How to delete the matching entries in sqlite

Question

I want to delete an row from sqlite but my senario is when i first enter a text field it should be inserted to the table and when I again enter the text field with same value it should not be entered as second row,It should be deleting the first entry and get updated and table Id should remain 1 only.

I dont know how to do it,Please suggest me as I doing first time the database.

Thanks.

Table creation:

            "CREATE TABLE secure" + DatabaseContract.RetailDatabaseEntry.TABLE_NAME_SETTINGS + " (" +
                    DatabaseContract.RetailDatabaseEntry.COLUMN_ID_SETTINGS+ " INTEGER PRIMARY KEY AUTOINCREMENT," +
                    DatabaseContract.RetailDatabaseEntry.COLUMN_FIRSTNAME + " TEXT UNIQUE ON CONFLICT REPLACE,"+
                    DatabaseContract.RetailDatabaseEntry.COLUMN_LASTNAME +" TEXT,"+
                    DatabaseContract.RetailDatabaseEntry.COLUMN_PHONENUMBER +" TEXT,"+
                    DatabaseContract.RetailDatabaseEntry.COLUMN_EMAILADDRESS +" TEXT,"+
                    DatabaseContract.RetailDatabaseEntry.COLUMN_ADDRESSLINE1 +" TEXT,"+
                    DatabaseContract.RetailDatabaseEntry.COLUMN_ADDRESSLINE2 +" TEXT,"+
                    DatabaseContract.RetailDatabaseEntry.COLUMN_LOCALITY +" TEXT,"+
                    DatabaseContract.RetailDatabaseEntry.COLUMN_CITY +" TEXT,"+
                    DatabaseContract.RetailDatabaseEntry.COLUMN_PINCODE +" TEXT" + 
                    ")";

What is TEXT_TYPE ?.. Better put the "TEXT_TYPE" outside the quotes — amalBit, Nov 16 '13 at 06:50
It is TEXT..I am having different class where I created all tables..........private static final String TEXT_TYPE = " TEXT"; — Nikhilkumar Waghaye, Nov 16 '13 at 06:53
But when I try to use this type of table creation,it giving me syntax error.. — Nikhilkumar Waghaye, Nov 16 '13 at 07:18
Actually what now happenning is when I am inserting firstname row id is not incrementing but it is not updating I want that it should be update also.. — Nikhilkumar Waghaye, Nov 16 '13 at 07:41

score 0 · Answer 1 · answered Nov 16 '13 at 06:18

0

You need to add UNIQUE constraint to the column of the table you want dont want to have duplicated values. like below

COLUMN_NAME+ " TEXT UNIQUE, "

answered Nov 16 '13 at 06:18

AAnkit

27,299
12
60
71

score 0 · Answer 2 · answered Nov 16 '13 at 06:19

0

Its not as tough as you think. While you create your database, make the value uniique like:

db.execSQL("CREATE TABLE secure (" +
            "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "name TEXT UNIQUE ON CONFLICT REPLACE," +
            "value TEXT" +
            ");");

NOw when you try to insert same "name" again and again in the above table, sqlite will automatically insert and replace for you. Thus no duplicate values.

answered Nov 16 '13 at 06:19

amalBit

12,041
6
77
94

like if I am having 4-5 columns in my table,and by making one column unique can I update the entire row,if I get duplicate text value? – Nikhilkumar Waghaye Nov 16 '13 at 06:23
I tried but it is not working,please check my creation of table..I am updating my question. – Nikhilkumar Waghaye Nov 16 '13 at 06:42

How to delete the matching entries in sqlite

2 Answers2