forum

Home / DeveloperSection / Forums / Problem in inserting value where not exist in database:

Ask Question

Problem in inserting value where not exist in database:

Anonymous User250201-Oct-2015

I want to insert value where value is not present in database table,

problem in this code:

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

exists using Sqlite3?
EDIT:
INSERT OR IGNORE

Is not working in my case

sqlite database  sqlite  sqlite3 
Updated on 27-May-2023
Anonymous User

Anonymous User

Follow
I am a content writter !

Can you answer this question?

Answer

2 Answers

Aryan Kumar

Aryan Kumar

27-May-2023

If you are facing difficulties inserting values into an SQLite database when the value does not already exist, you can use the following approach:

1. Define a unique constraint:
  - Ensure that the column or columns where you want to insert unique values have a unique constraint defined. This constraint prevents duplicate values from being inserted into the column(s). You can specify the unique constraint when creating the table or alter the table later to add the constraint. For example:

    CREATE TABLE my_table (
        column_name INTEGER UNIQUE,
        other_columns...
    );

2. Use the INSERT OR IGNORE statement:
  - SQLite provides the `INSERT OR IGNORE` statement, which allows you to insert a row into a table only if it does not violate any unique constraints. If a conflict occurs, the statement will silently ignore the insertion without raising an error. Here's an example:

    INSERT OR IGNORE INTO my_table (column_name, other_columns...)
    VALUES (value1, value2, ...);

  - Replace `my_table` with the name of your table and provide the appropriate column names and values.

By combining a unique constraint with the `INSERT OR IGNORE` statement, you can ensure that only unique values are inserted into the specified column(s). If a value already exists, it will be ignored, and no new row will be inserted.

Note: If you want to handle conflicts differently, you can also use other conflict resolution methods like `INSERT OR REPLACE` or `INSERT OR ABORT`, depending on your requirements.

Additionally, ensure that you are properly handling errors and exceptions in your code. If an error occurs during the insertion, it's important to capture and handle any exceptions raised by the SQLite library to avoid unexpected behavior or data inconsistencies.

Tarun Kumar

Tarun Kumar

01-Oct-2015

you can use this method to check the value is exist in database or not.
I hope it will be helpful for 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;
}

Liked By