CODEWITHSUNDEEP
pythonsqlsqlite
Catgirl21
Catgirl21

Reputation: 37

How to insert a value into the table or update it if it's already in the table

I am setting up a database with two fields, user_id and count, and I want to have an ability to insert this pair of values or increment count by 1 if the record with the same user_id is already in the table.

I have already tried many solutions, for example, INSERT INTO ... ON DUPLICATE KEY UPDATE ..., but they don't work for me. It throws an error that says:

sqlite3.OperationalError: near "ON": syntax error

I am running SQLite 3.21.0 on python 3.7.

This is the code I have:

def add_warning(id):
    sql = f"""INSERT INTO warnings (user_id, count) VALUES({id}, 1) ON CONFLICT(user_id) DO UPDATE SET count=count+1"""
    cursor.execute(sql)

I expect to get the count value updated when executing the query, but it throws an error when I try to do that.

Upvotes: 1

Views: 211

Answers (3)

Benedic Cater
Benedic Cater

Reputation: 25

You can also try this. This works on SQL Server but I haven't tried this on SQLite

sql = "IF EXISTS (select user_id FROM warnings WHERE user_id={0}) " + \
"BEGIN " + \
"UPDATE warnings SET count = count + 1  WHERE user_id={0} " + \
"END " + \
"ELSE " + \
"INSERT INTO warnings (user_id, count) VALUES ({0}, 0 )".format(id)

Upvotes: 0

forpas
forpas

Reputation: 164064

Assuming that user_id is the primary key of the table you can use (INSERT OR) REPLACE:

sql = "REPLACE INTO warnings (user_id, count) VALUES(?, COALESCE((SELECT count + 1 FROM warnings WHERE user_id = ?), 1))"
cursor.execute(sql1, (id, id,))

See the demo.

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

The upsert syntax you are trying to use is only supported on SQLite versions 3.24.0 or later. This explains why your code is not working, and here is a workaround:

sql1 = "INSERT OR IGNORE INTO warnings (user_id, count) VALUES (?, 0)"
cursor.execute(sql1, (id,))
sql2 = "UPDATE warnings SET count = count + 1 WHERE user_id = ?"
cursor.execute(sql2, (id,))

The logic here is to do an initial insert of a user record with a zero count only if that user is not already found. Then, we always update the count by one no matter what.

Upvotes: 2

Related Questions