I have been trying to update some values in a sqlite3 table but I am
having loads of problems with the `sqlite3.OperationalError: near
"GROUP": syntax error. I have been following the documentation but I
am not been very successful to correct the error. What I am trying to
do is to find the all_numbers with a value =0 and updated with the
max(value)+1 I just need to say that I am new creating databases so
any help would be highly appreciated. Thanks in advance.
> def update_tables(connection): #function to update table
> c = connection.cursor() # i have a separated def to create the connection
> data = """SELECT id, cps, all_numb FROM all_records""" #this is my column selection
> c.execute(data)
> rows = (c.fetchall())
> # print(rows)
> _list = []
> for row in rows: # I try to use a python loop to iterate through the rows
> id = row[0]
> cps = row[1]
> all_numb = row[2]
> if cps not in _list:
> _list.append(cps)
> if cps in _list: #here I tried a for loop but it does not work
> sq3 = """UPDATE all_records # this is the update selection
> SET all_numb =(SELECT MAX(all_numb) +1)
> WHERE cps =cps AND all_numb = 0
> GROUP BY cps"""
> c.execute(sq3)
> connection.commit()
> c.fetchall()
>Solution :
I think this is what you’re trying to do:
UPDATE all_records AS r1
SET all_numb = last_numb + 1
FROM (
SELECT r2.cps, MAX(r2.all_numb) AS last_numb
FROM all_records AS r2
GROUP BY r2.cps
)
WHERE r1.cps = r2.cps AND all_numb = 0
See the documentation of UPDATE FROM
I’m not sure why you’re doing this in a for loop, since you’re not using any of the variables in the loop. This updates the entire table at once, so it doesn’t need to be in a loop.