Python MySQL Update Table
In this page:
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
from unittest.mock import MagicMock
conn = MagicMock()
cursor = conn.cursor()
cursor.execute(
"UPDATE customers SET address = %s WHERE address = %s",
("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
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
from unittest.mock import MagicMock
conn = MagicMock()
cursor = conn.cursor()
cursor.execute(
"UPDATE customers SET address = %s, city = %s WHERE id = %s",
("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
from unittest.mock import MagicMock
conn = MagicMock()
cursor = conn.cursor()
cursor.execute("UPDATE customers SET city = %s WHERE city = %s", ("Austin", "austin"))
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
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()
print("Update failed, rolled back:", e)- 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.
- Forgetting conn.commit() after an UPDATE, leaving the change unsaved once the connection closes.
- Swapping the SET and WHERE clause values by mistake, accidentally setting a column to the value meant for filtering, or vice versa.
- 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.
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:
- Python MySQL Get Started
- Python MySQL Create Database
- Python MySQL Create Table
- Python MySQL Insert Into Table
- Python MySQL Select From Table
- Python MySQL Select With a Filter
- Python MySQL Order By
- Python MySQL Delete Record
- Python MySQL Drop Table
- Python MySQL Update Table
- Python MySQL Limit
- Python MySQL Join