How to increment quantity column if record exists, else insert record?

Question

I've got the below sqlite database:

CREATE TABLE `test` (
    `idx`   INTEGER PRIMARY KEY AUTOINCREMENT,
    `qty`   INTEGER,
    `brand` TEXT,
    `type`  TEXT
);

insert into test (qty, brand, type) values (1, "Merc", "petrol");
insert into test (qty, brand, type) values (1, "Toyota", "diesel");

I want to insert another record but if it exists then I want to increase the qty field, so:

insert into test (qty, brand, type) values (1, "Merc", "diesel");

so would just increase the qty field by one, and

insert into test (qty, brand, type) values (1, "Merc", "petrol");

would insert a new record.

Answer 1

SQLite is an embedded database, i.e., it is designed to be used together with an application. The easiest way is to put the logic into the application:

db.execute("UPDATE test SET qty = qty + 1 WHERE brand = ? AND type = ?",
           ["Merc", "diesel"])
if db.rows_affected == 0:
    db.execute("INSERT ...")