SQLite insert if not exist else increase integer value

Question

I need a way to insert a value if the value doesn't exist and if it does it should increase that value by another variable.

This is what my table looks like:

CREATE TABLE IF NOT EXISTS table(
      Id INTEGER PRIMARY KEY,
      Date TEXT,
      Quantity INTEGER

Whenever i add a date to the database, with a quantity i want it to, add the quantity to the "quantity" in table. So every "Date" would only have 1 "Quantity" assigned.

Just a small example:

INSERT INTO Table (Date, Quantity) VALUES('%s', Quantity+%s)) % ('12/5/2013', 20);

If there already is a record that looks like ('12/5/2013', 5) it would be ('12/5/2013', 25) instead.

I found 1 very similar question, but i don't understand, how i also make the integer increase.. Another Question

Could you specify what exactly you don't understand in the answer you linked to? The replace or insert sounds pretty self-explenary. The coalesce is new to me but according to the docs it returns the first non-null argument passed into, So if the select returns null (as there is no record for given date yet) the second paramter is used as a fallback. In your case you just don't want coalesce(SELECT val+1 …, 1) but something like coalesce(SELECT quantity+quantity …, quantitty) sqlite.org/lang_corefunc.html — nuala
– nuala, Commented Mar 2, 2015 at 18:07

Katie Rust · Accepted Answer · 2015-03-02 18:52:03Z

6

If you want to have only one column for each date, then you should declare it as UNIQUE, or even as the PRIMARY KEY for the table.

If you do that, then you can declare the Quantity column as INTEGER NOT NULL DEFAULT 0. Your table would then be defined as:

CREATE TABLE IF NOT EXISTS table (
    Date TEXT PRIMARY KEY,
    Quantity INTEGER NOT NULL DEFAULT 0
);

Then when you want to add to a specific date, you run:

# assuming 'db' is your sqlite3 database
date_to_insert = '12/5/2013'
amount_to_add = 20
db.execute('INSERT OR IGNORE INTO table(Date) VALUES(?)', (date_to_insert,))
db.execute('UPDATE table SET Quantity = Quantity + ? WHERE Date = ?', (amount_to_add, date_to_insert))

answered Mar 2, 2015 at 18:52

Katie Rust

1763 bronze badges

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

1 Comment

RasmusGP Over a year ago

Awesome that works, i declared Date as UNIQUE and Id as PRIMARY. Works like a charm.

Collectives™ on Stack Overflow

SQLite insert if not exist else increase integer value

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related