Mysql Question posted in
The official MySQL documentation can be found here.

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

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
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    the issue here is that the UPDATE statement in MySQL does not support the same syntax like INSERT statement with column lists and VALUES. Here I would suggest to specify the columns to be updated and their corresponding values directly in the SET clause

    • SET lists each column to update and assigns it a value
    • %s as placeholders for the values

    here is the query

    import mysql.connector

item_id = 123
val1 = 'value1'
val2 = 'value2'
val3 = 'value3'
val4 = 'value4'


query = (f"UPDATE MyTable "
         f"SET col1 = %s, col2 = %s, col3 = %s, col4 = %s "
         f"WHERE item_id = %s")


items = (val1, val2, val3, val4, item_id)

cursor.execute(query, items)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search