PyMySQL中执行带INNER JOIN的UPDATE语句失败，请求排查

Got it, let's break down why your query isn't working and fix it up—your MySQL UPDATE JOIN syntax has an incomplete relationship between the two tables, which is causing the issue.

What's Off With the Original Code? #

Take a look at your current query:

query = """UPDATE user INNER JOIN identification_card ON user.id = %s SET user.id_type = %s, user.validity = %s"""
data = (identification_card.user_id, identification_card.card_type, identification_card.expiry_date)

The ON user.id = %s part only filters the user table to a single ID—it doesn't actually link the user table to the identification_card table. MySQL needs a clear relationship between the two tables in the ON clause to execute an UPDATE JOIN, so this incomplete condition breaks the query.

Corrected Query Options #

Here are two valid, clean ways to rewrite this:

Option 1: Separate Join Relationship and Filter (Recommended)

This approach keeps your table connection logic separate from the specific record you want to update, making the query easier to read:

query = """
UPDATE user
INNER JOIN identification_card
ON user.id = identification_card.user_id  # This properly links the two tables
SET user.id_type = %s, user.validity = %s
WHERE user.id = %s  # Target the specific user you need to update
"""
# Reorder data to match the placeholder order in the query
data = (identification_card.card_type, identification_card.expiry_date, identification_card.user_id)
cursor.execute(query, data)
# Don't forget to commit if autocommit is disabled!
connection.commit()

Option 2: Combine Filter Into the Join Clause

If you prefer, you can fold the user ID filter directly into the ON clause:

query = """
UPDATE user
INNER JOIN identification_card
ON user.id = identification_card.user_id AND user.id = %s
SET user.id_type = %s, user.validity = %s
"""
data = (identification_card.user_id, identification_card.card_type, identification_card.expiry_date)
cursor.execute(query, data)
connection.commit()

Quick Additional Checks #

• Double-check that your column names (id_type, validity, user_id, etc.) match exactly what's in your database—typos are a super common gotcha.
• If your connection doesn't have autocommit turned on, always call connection.commit() after executing the query, otherwise your changes won't save to the database.
• Verify that identification_card.user_id actually exists in the user table—if there's no matching record, the UPDATE will run but affect 0 rows.

内容的提问来源于stack exchange，提问作者Kharisa Mae G. Macaraig