How to format a SQL update query for many columns in python?

Question

I'm trying to update a table in MySQL 8.0.35, but it seems like it doesn't like the way I'm formatting this query. I can insert into tables this way, but I get an error when I try to use it to update

import mysql.connector


item_id = 123
query = (f"UPDATE MyTable "
         f"SET (col1, col2, col3, col4) "
         f"VALUES (%s, %s, %s, %s) "
         f"WHERE item_id = {item_id}")
items = (val1, val2, val3, val4)
cursor.execute(query, items)

But I keep getting this error:

ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(col1, col2, col3, col4' at line 1

Answer 1

What I think is wrong in your query is that the UPDATE statement does not accept multiple columns. You might have to do following in order to update values.

import mysql.connector

item_id = 123
query = (f"UPDATE MyTable "
     f"SET col1 = %s, col2 = %s, col3 = %s, col4 = %s "
     f"WHERE item_id = {item_id}")
items = (val1, val2, val3, val4)
cursor.execute(query, items)

By setting val1, val2, val3 and val4 separately, you will be able to run your query without any problem.