Errors when attempting to update multiple column values in a MySQL DB using multiple WHERE clause values

Question

I'm currently getting an error when attempting to update two columns of my MySQL database using mysql.connector and python 3.6. When I execute the command below I get:

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 'order='1' WHERE (match_id='2051673' AND gametime=80 AND event_name='Pass')' at line 1

But, as far as I can tell, my command is perfectly legit. What am I doing incorrectly? Thanks!

for item in pass_list:
    query = """UPDATE events SET event_key=%s AND order=%s 
               WHERE (match_id=%s AND gametime=%s AND event_name=%s)"""
    values = (item[0],item[7],item[1],item[2],item[3])
    cur.execute(query, values)
conn.commit()
conn.close()

The problem, as it happens, is that order is a SQL keyword. You can put backticks around it: `order`. — mechanical_meat
– mechanical_meat, Commented May 25, 2020 at 16:16
Thank you so much! You're right and it never occurred to me, but should have. — doogra
– doogra, Commented May 25, 2020 at 16:46
You're welcome. The error message isn't particularly helpful in this case, haha. — mechanical_meat
– mechanical_meat, Commented May 25, 2020 at 16:52
Got it....Needed to use a comma instead of an AND. Correct syntax was "UPDATE events SET events.event_key=%s, events.order_id=%s WHERE".... — doogra
– doogra, Commented May 25, 2020 at 18:59

mechanical_meat · Accepted Answer · 2020-05-25 18:07:52Z

1

For good or bad, order is a SQL keyword. You can put backticks around it:

`order`

answered May 25, 2020 at 18:07

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pablo Camara Over a year ago

this was exactly my problem hehehe

Collectives™ on Stack Overflow

Errors when attempting to update multiple column values in a MySQL DB using multiple WHERE clause values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related