how to avoid duplicate insertion sqlite-CodePudding

Hi i added array of data as rows in local database which is got from backend, now i got new data with existing data from backend, i'm trying to add this data to local database but duplicates are creating

"CREATE TABLE IF NOT EXISTS "  
this.GENRES_TABLE_NAME  
" "  
"( "  
"_id integer primary key autoincrement, "  
"genre_id integer, "  
"genre_name text, "  
" )";

i have this data in local database

    {
        "_id":1,
        "genre_id": 43,
        "genre_name": "*Born 1920 - 1925"
    },
    {
        "_id":2
        "genre_id": 44,
        "genre_name": "1920's and Before"
    },
    {
        "_id":3
        "genre_id": 45,
        "genre_name": "1930's"
    }

this is my new data

    {
        "genre_id": 43,
        "genre_name": "*Born 1920 - 1925"
    },
    {
        "genre_id": 44,
        "genre_name": "1920's and Before"
    },
    {
        "genre_id": 111,
        "genre_name": "new collection"
    }

i want to add genre_id:111 record to local database, but my query adding all records to local database, this is my query

   let insertSQL = `INSERT INTO ${this.GENRES_TABLE_NAME} (genre_id 
    ,genre_name) VALUES `;
      genres.forEach((genre, index) => {
        insertSQL  = `(${genre.id},"${genre.name}")`;
        if (index < genres.length - 1) {
          insertSQL  = ",";
        }
      });
      insertSQL  = ";";
      db.executeSql(insertSQL)

please help me

CodePudding user response：

Since genre_id already has a UNIQUE constraint, you should be able to use the SQLite-specific syntax INSERT OR IGNORE INTO <table>, silencing what would otherwise be a unique constraint violation error.

Also, I notices you're not using parameterized queries, which isn't safe. To protect your app from SQL injection attacks, use parameterized queries everywhere.