how do i increment a value of column with condition?

Question

my table looks something like this

id effect1(int) effect2(int) effect3(int) 
1     0           1               5
2     1           0               0

i have a function, which return 1to3 int value representing effect1,effect2,effect3 and "id"

i am trying to increment value of each effect by 1 when my function's returning "id" matches the "id" from my dataset otherwise creates new row and increase value of effect

for example

if my function returns (1,3) my table will become

id effect1(int) effect2(int) effect3(int) 
1     0           1               6
2     1           0               0

if my function returns (3,3) my table will become

id effect1(int) effect2(int) effect3(int) 
1     0           1               6
2     1           0               0
3     0           0               1

i have tried the following code using python, but failed. can someone please help me out here

i = (1,3) # the returning value from my function
if i[1] == 1:
    sql = "update test set effect1=effect1+1 WHERE id= ({})".format((str(i[0])))
    db.execute_query(myDb, sql)
elif i[1] == 2:
    sql = "update test set effect2=effect2+1 WHERE id= ({})".format((str(i[0])))
    val = (i[0],)
    db.execute_query(myDb, sql )
elif i[1] == 3:
    sql = "update test set effect3=effect3+1 WHERE id= ({})".format((str(i[0])))
    val = (i[0],)
    db.execute_query(myDb, sql )


def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query successful")
    except Exception as err:
        print(f"Error: '{err}'")

score 2 · Answer 1 · answered Mar 11 '22 at 17:06

Assuming that id is the primary key of the table or it is defined as unique, you can use MySql's INSERT ... ON DUPLICATE KEY UPDATE Statement like this:

i = (1, 3)
sql = """
INSERT INTO test (id, effect1, effect2, effect3)
SELECT id, (n = 1), (n = 2), (n = 3) FROM (SELECT ? id, ? n) t
ON DUPLICATE KEY UPDATE effect1 = effect1 + (n = 1), 
                        effect2 = effect2 + (n = 2), 
                        effect3 = effect3 + (n = 3);
"""
db.execute_query(myDb, sql, i)

def execute_query(connection, query, i):
    cursor = connection.cursor()
    try:
        cursor.execute(query, i)
        connection.commit()
        print("Query successful")
    except Exception as err:
        print(f"Error: '{err}'")

See a demo of how this works in MySql.

thanks man, its working :D – mohammad eunus Mar 11 '22 at 19:32 — mohammad eunus, Mar 11 '22 at 19:32

score 1 · Accepted Answer · answered Mar 10 '22 at 18:45

1

In your implementation, you did not check if the id already exists or not.

I updated your code with an SQL statement that performs insertion/update as you mentioned-

in case the id exists then only the specific effect needs to be increased
if id does not exist then a new row with that id needs to be added/inserted into the test table)

I also replaced the variable i (a list of two elements), with two variables named id and effect_no to improve the readability of the code. The if else statements are also not needed as the effect_no can be used/incorporated into the sql statement itself.

Following is the updated code:

id, effect_no = (1,3) # the returning value from your function
sql = f"""INSERT INTO test (id, effect1, effect2, effect3)
VALUES ({id}, 0, 0, 0)
ON DUPLICATE KEY UPDATE effect{effect_no} = effect{effect_no} + 1"""
db.execute_query(myDb, sql)

def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query successful")
    except Exception as err:
        print(f"Error: '{err}'")

Also, I found the INSERT ... ON DUPLICATE KEY UPDATE SQL statement here https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/#using-insert--on-duplicate-key-update

answered Mar 10 '22 at 18:45

ubaid shaikh

453
3
7

but you are assigning the other values as zero. The other data will be lost if I do that. – mohammad eunus Mar 11 '22 at 16:01
please, could you possibly share which other values are being assigned as `0`? Currently, when there is no row in the table with a given `id`, a new row with that given `id` and `0` value for the effects is being assigned. – ubaid shaikh Mar 11 '22 at 18:27
My apologies, your code was actually working. However, I had one issue here, which I have resolved. Since you assigned the other values a zero, each effect would have 1 fewer, as you started from 0. the changes i made is `idd, effect_no = (1, 3) # the returning value from my function sql = f""" INSERT INTO testy (id, effect{effect_no}) VALUES ({id}, 1) ON DUPLICATE KEY UPDATE effect{effect_no} = effect{effect_no} + 1 """` – mohammad eunus Mar 11 '22 at 19:18
`INSERT INTO test (id, effect1) VALUES (5, 1) ON DUPLICATE KEY UPDATE effect1 = effect1 + 1` – mohammad eunus Mar 11 '22 at 19:21
1

Great! Happy to help! – ubaid shaikh Mar 12 '22 at 17:41

how do i increment a value of column with condition?

2 Answers2