CODEWITHSUNDEEP
sqlsqlalchemy
Sy Ker
Sy Ker

Reputation: 2180

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;"]

Upvotes: 1

Views: 525

Answers (2)

Parfait
Parfait

Reputation: 107567

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

It looks like the update syntax you are using either is not supported at all, or at least is not supported on your version of SQLite. However, you may rewrite your update to use exists logic:

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

The raw SQL query would look something like this:

UPDATE yourTable tb
SET box = ?
WHERE EXISTS (SELECT 1 FROM question qu
              WHERE qu.id = tb.question_id AND
                    qu.number_a = ? AND qu.number_b = ?);

Upvotes: 1

Related Questions