Toggle values in sqlite3

Question

My table has a column named status with data type CHARACTER. I wish to do the following.

If a row does not exists for a primary key, insert 'active' in status. If a row already has an entry for a primary key, toggle the status. Use ative, not_active values for toggling.

Currently I am doing it by writing multiple queries. Is there any single query to do this like Mysql?

Answer 1

SQLite does not have control logic commands like IF because that would not make sense in an embedded database.

Implement the control logic in whatever language you're using to access the database:

db.begin_transaction()
result = db.execute("SELECT status FROM MyTable WHERE id = ?", [id]);
if result.length > 0:
    new_status = if result[0][0] == 'active': 'not_active' else: 'active'
    db.execute("UPDATE MyTable SET status = ? WHERE id = ?", [new_status, id])
else:
    db.execute("INSERT INTO MyTable(id, status) VALUES(?, ?)", [id, 'active'])
db.commit()

However, the toggle logic itself can be implemented with a CASE expression, so if you don't care too much about clarity, you can execute the following two commands instead, in this order (the UPDATE will do nothing if the record does not exist, and the INSERT OR IGNORE will do nothing if the new record would violate any unique constraint):

UPDATE MyTable
SET status = CASE status
             WHEN 'active' THEN 'not_active'
             ELSE               'active'
             END
WHERE id = ?;

INSERT OR IGNORE INTO MyTable (id, status) VALUES (?, 'active');