sqlite3.OperationalError:: syntax error when trying to UPDATE

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()

Answer

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.