Sqlite Insert or Replace query with increment of an integer field

Question

There are three fields(_id,name,count) in in a table name poet. I want to check if poet name exist in the table then update count field with increment of 1 or else insert a new record with the given name and count as 1. Here is my query. It is inserting null value for count if there name not found in the table. Although it is working good if I want to update already existing records.

insert or replace into poet (_id,Name, count) values (
   (select _id from poet where Name = "SearchName"),
   "SearchName",
    (select count from poet where Name = "SearchName")+ 1)

Garett · Accepted Answer · 2016-01-22 05:06:02Z

4

How about changing the query to use ifnull or coalesce.

insert or replace into poet (_id,Name, count) values (
   (select _id from poet where Name = "SearchName"),
   "SearchName",
    ifnull((select count from poet where Name = "SearchName"), 0) + 1)

answered Jan 22, 2016 at 5:06

Garett

16.9k5 gold badges58 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CL. · Accepted Answer · 2016-01-19 07:46:38Z

1

Don't try to be clever. Just use two commands:

db.beginTransaction();
try {
    db.execSQL("UPDATE poet SET count = count + 1 WHERE Name = ?",
               new Object[]{ searchName });
    if (DatabaseUtils.longForQuery(db, "SELECT changes()", null) == 0) {
        db.execSQL("INSERT INTO poet(Name, count) VALUES(?,?)",
                    new Object[]{ searchName, 1 });
    }
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

answered Jan 19, 2016 at 7:46

CL.

182k18 gold badges241 silver badges282 bronze badges

Collectives™ on Stack Overflow

Sqlite Insert or Replace query with increment of an integer field

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related