← Back to Python Course | Chapter 15: Python & MySQL | Lesson 10 of 12

Python MySQL Update Table

Existing rows are modified in place with an UPDATE SQL statement -- specifying which column(s) to change with SET, and (critically) which rows to change with a WHERE clause, since an UPDATE with no WHERE modifies every row in the table.

In this page:

  1. Basic UPDATE Statement
  2. The Danger of Updating Without WHERE
  3. Updating Multiple Columns at Once
  4. Updating Multiple Rows at Once
  5. Handling Update Errors Safely
Syntax
python 
sql = "UPDATE table_name SET column = %s WHERE condition_column = %s"
cursor.execute(sql, (new_value, condition_value))
connection.commit()

Basic UPDATE Statement

cursor.execute("UPDATE customers SET address = %s WHERE address = %s", ("Canyon 123", "Valley 345")) changes the address column, but only in the row(s) where the old address currently matches "Valley 345" -- every other row remains untouched.

Note: Always pass both the new value and the WHERE condition's value through the parameterized tuple, in the same order they appear as %s placeholders in the SQL string.
Warning: An UPDATE's WHERE clause matching multiple rows updates all of them identically -- make sure the condition is specific enough if only one row should change.

Example: Basic UPDATE Statement

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute(
    "UPDATE customers SET address = %s WHERE address = %s",  # only rows matching the WHERE are changed
    ("Canyon 123", "Valley 345"),
)
conn.commit()
print("Matching row(s) updated")

The Danger of Updating Without WHERE

UPDATE customers SET address = "Unknown" with no WHERE clause is syntactically valid SQL that overwrites the address column in every single row of the table with the same value -- one of the costliest possible mistakes in database work, alongside an unqualified DELETE.

Note: Before running any UPDATE statement in a real script, explicitly confirm a WHERE clause is present and scoped to exactly the row(s) intended.
Warning: Testing the WHERE clause first as a SELECT (confirming exactly which rows match) before running the actual UPDATE is a cheap, effective safety habit.

Example: The Danger of Updating Without WHERE

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
# DANGER: no WHERE clause -- this overwrites every row's address
sql = "UPDATE customers SET address = %s"
print("Always double-check for a WHERE clause before running:", sql)

Updating Multiple Columns at Once

Several columns can be set in a single UPDATE statement by separating each column = %s assignment with a comma inside the SET clause -- all listed columns are changed together, atomically, in the same statement.

Note: Combine related column changes into a single UPDATE statement rather than several separate ones, both for efficiency and to keep the change atomic.
Warning: The order of %s placeholders in the SET clause must match the order of values in the parameter tuple, and the final WHERE clause's placeholder comes last in that same tuple.

Example: Updating Multiple Columns at Once

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute(
    "UPDATE customers SET address = %s, city = %s WHERE id = %s",  # both columns changed together
    ("Canyon 123", "Denver", 1),
)
conn.commit()
print("Two columns updated in one statement")

Updating Multiple Rows at Once

An UPDATE's WHERE clause is not limited to matching a single row -- a broader condition (like a comparison operator or LIKE pattern) can match and update several rows together in one statement, exactly as a broader WHERE would return several rows in a SELECT.

Note: Test the broader WHERE condition first as a SELECT to confirm exactly how many rows it matches, before running an UPDATE intended to affect several rows at once.
Warning: A broad WHERE condition in an UPDATE can affect far more rows than intended if it is less specific than assumed -- the same risk as an overly broad DELETE.

Example: Updating Multiple Rows at Once

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("UPDATE customers SET city = %s WHERE city = %s", ("Austin", "austin"))  # broader WHERE can match several rows
conn.commit()
print("All rows matching the WHERE clause updated together")

Handling Update Errors Safely

Wrapping an UPDATE in a try-except block catching mysql.connector.Error -- and calling conn.rollback() in the except branch -- lets you handle failures like a constraint violation cleanly without crashing, keeping the database in a consistent state.

Note: Pair every write operation (UPDATE included) with a try/except and an explicit rollback() on failure, treating each write as an all-or-nothing unit.
Warning: A partially-applied multi-statement UPDATE sequence that fails midway, without a rollback, can leave the database in an inconsistent, half-updated state.

Example: Handling Update Errors Safely

python 
from unittest.mock import MagicMock

class Error(Exception):
    pass

conn = MagicMock()
cursor = conn.cursor()
cursor.execute.side_effect = Error("Constraint violation")
try:
    cursor.execute("UPDATE customers SET id = %s WHERE id = %s", (2, 1))
    conn.commit()
except Error as e:
    conn.rollback()  # keeps the database consistent after a failed update
    print("Update failed, rolled back:", e)
Related Topics

Python MySQL Drop Table · Python MySQL Select From Table · Python MySQL Insert Into Table · Python MySQL Create Table

{# common_mistakes/chapter_summary/browser_support: on Hindi pages the view already swaps in the hi_ translation fields (or blanks these out if untranslated), so this renders correctly for both languages without a lang_code check here. #}
Common Mistakes
  1. Running UPDATE tableName SET column = value with no WHERE clause, which changes that column in every single row of the table instead of just the intended one.
  2. Forgetting conn.commit() after an UPDATE, leaving the change unsaved once the connection closes.
  3. Swapping the SET and WHERE clause values by mistake, accidentally setting a column to the value meant for filtering, or vice versa.
Chapter Summary
  • UPDATE table SET column = %s WHERE condition changes a column's value only in rows matching the condition.
  • Omitting the WHERE clause updates every row in the table -- always double-check it is present.
  • conn.commit() must be called after UPDATE to permanently save the change.
Browser Support

UPDATE syntax is standard SQL, executed identically across all mysql-connector-python versions.

🔒

Chapter Quiz — Complete all 12 topics to unlock

0/12 topics done

Complete these topics first:

← Previous Lesson Python MySQL Drop Table Next Lesson → Python MySQL Limit

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.