INSERT VALUE WHERE NOT EXIST with Sqlite?

Question

Does this statement:

INSERT INTO table (...)
VALUES (...) 
WHERE NOT EXISTS (...)

exists using Sqlite3?

EDIT:

INSERT OR IGNORE

Is not working in my case

you can use defaults while creating tables and finally where ever that default value is there you can insert your value — Aniruddha K.M, Sep 28 '15 at 04:12
@war_Hero But what happen if the value I'm trying to add is already on the table? I need to check that — Virthuss, Sep 28 '15 at 04:14
you were doing where not exist right that means where values dont exist — Aniruddha K.M, Sep 28 '15 at 04:27
WHERE would be part of a SELECT statement. See the [documentation](http://www.sqlite.org/lang_insert.html). — CL., Sep 28 '15 at 07:35
Hi CL, Sorry I didnt see your comment. You are right, I will edit my question. — Virthuss, Sep 28 '15 at 07:50

score 0 · Answer 1 · answered Sep 28 '15 at 05:10

0

please use this method to check if value exist in database or not.its very easy and proper way..i hope its useful to you.

 public boolean isRecordExist(String TableName, String columnName,
                                        String value) {
           DbHelper  dbHelper = new DbHelper(this);
            dbHelper.createDataBase();
            SQLiteDatabase sqldb = dbHelper.openDataBase();
        String Query = "Select * from " + TableName + " where " + columnName
                + "='" + value + "'";
        Cursor cursor = sqldb.rawQuery(Query, null);
        if (cursor != null && cursor.getCount() <= 0) {
            cursor.close();
            return false;

        } else if (cursor != null) {
            cursor.close();
        }
        return true;

    }

answered Sep 28 '15 at 05:10

dipali

10,966
5
25
51

Thanks for that :) However, I'm looking also for a way to do that without using any java code ( where the aim is to generate my db using only sqlite and then use this db in android studio, in order to avoid the device to execute any request conerning the creation of the db ) – Virthuss Sep 28 '15 at 05:51
i think its not possible without java code,..if you have find solution for that then plz put in to this. – dipali Sep 28 '15 at 06:15
Sure. I'm gonna edit my main post. Thanks again for your android solution, it will be useful for me during my further tasks. – Virthuss Sep 28 '15 at 06:21
if its really helpful then upvote it. – dipali Sep 28 '15 at 07:29
This does not answer the question. And using [queryNumEntries()](http://developer.android.com/reference/android/database/DatabaseUtils.html#queryNumEntries%28android.database.sqlite.SQLiteDatabase,%20java.lang.String,%20java.lang.String%29) would be simpler. – CL. Sep 28 '15 at 07:32
public void insertCommand(ContentValues values) { if (!isRecordExist(db,table, values)) { //insert you record } } private boolean isExist(SQLiteDatabase db,String table, ContentValues data) { StringBuilder sql = new StringBuilder(); Set keys = data.keySet(); for(String key : keys){ sql.append(key).append("=").append(data.get(key)).append(" AND "); } //Remove last appended " AND " before proceed String q= sql.toString(); return DatabaseUtils.queryNumEntries(db,table,q) == 0 ? false : true; } – Mudassar Sep 28 '15 at 11:06
This cannot be done in sqllite but you can do some work in java like I have posted above. You can test it this way synchronized (LOCK) { SQLiteDatabase db = getdb(); ContentValues values = new ContentValues(); values.put(DBInterfaces.COMMANDS.COMMAND, "test"); values.put(DBInterfaces.COMMANDS.VALUE, "test"); insertCommand(values); db.close(); } – Mudassar Sep 28 '15 at 11:08

INSERT VALUE WHERE NOT EXIST with Sqlite?

1 Answers1