FMDB insert or replace do multiple entries

Question

I have an sqlite3 table, every time I run my code, it adds multiple entries. How can I change this query to add only one entry?

CREATE TABLE "GroupTable" (
    "id"    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "memberId"  TEXT NOT NULL,
    "adminId"   TEXT NOT NULL,
    "name"  TEXT NOT NULL
);

The method below shows how I am adding data:

func addDatatoList(info: ListModel ) -> Bool
    {
        sharedInstance.database!.open()
  
        let isInserted = sharedInstance.database!.executeUpdate("INSERT or REPLACE INTO GroupTable(adminId, memberId, name) VALUES (?,?,?)", withArgumentsIn: [ info.adminId,info. memberId,info.name])
        sharedInstance.database!.close()
        return isInserted
    }

Answer 1

You’re not supplying an id value, so INSERT OR REPLACE will never replace. You theoretically could add id parameter, passing NSNull() if the id value is NULL

func addDatatoList(info: ListModel) -> Bool {
    guard let db = sharedInstance.database else { return false }

    db.open()
    defer { db.close() }

    let sql = "INSERT or REPLACE INTO GroupTable(id, adminId, memberId, name) VALUES (?, ?, ?, ?)"
    let values: [Any] = [info.id ?? NSNull(), info.adminId, info.memberId, info.name]
    let isInserted = sharedInstance.database!.executeUpdate(sql, withArgumentsIn: values)

    return isInserted
}

That having been said, if you did INSERT, you probably want to retrieve the row’s auto increment id value:

func addDatatoList(info: ListModel) -> Bool {
    guard let db = sharedInstance.database else { return false }

    db.open()
    defer { db.close() }

    let sql = "INSERT or REPLACE INTO GroupTable(id, adminId, memberId, name) VALUES (?, ?, ?, ?)"
    let values: [Any] = [info.id ?? NSNull(), info.adminId, info.memberId, info.name]
    let isInserted = sharedInstance.database!.executeUpdate(sql, withArgumentsIn: values)

    if isInserted, info.id == nil {
        let id = db.lastInsertRowId

        // e.g., if `ListModel` is a reference type and `id` is mutable, 
        // you might update the value, e.g.

        info.id = Int(id)
    }

    return isInserted
}

But, then again, if you're now programmatically determining whether you inserted or updated, you might as well have two SQL statements, one for INSERT and one for UPDATE.