SQL Update statement: OperationalError near “FROM”

Replacing box = with tb.box = shifts the error to the ‘.’:

query = (f'UPDATE tb '
         f"SET box = '{box_update}' "
         f'FROM {table} tb '
         'INNER JOIN question qu ON qu.id = tb.question_id '
         f'WHERE qu.number_a = {num_a} AND qu.number_b = {num_b};')

Error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "FROM": syntax error [SQL: "UPDATE tb SET box = '0' FROM addition tb INNER JOIN question qu ON qu.id = tb.question_id WHERE qu.number_a = 1 AND qu.number_b = 9;"]

Answer

Actual implementation may not be fully adhered even with latest SQLite to support UPDATE-FROM. Specifically, docs do not indicate:

• JOIN in outer query is supported in UPDATE.
• FROM table should not repeat table in UPDATE.
• Table alias alone in UPDATE may not be allowed. Possibly no alias for updated table should be used.

Consider below adjustment aligned to example in docs. Below demonstrates parameterization with sqlite3 raw cursor. Adjust to however you run with sqlalchemy.

q = f'''UPDATE {table}
        SET box = ? 
        FROM question qu 
        WHERE qu.id = {table}.question_id
          AND qu.number_a = ?
          AND qu.number_b = ?;
     '''

cursor.execute(q, (box_update, num_a, num_b))
conn.commit()