Update Records

By the end of this lesson, you will be able to safely modify existing data in a database table using Python's SQL UPDATE statement with parameterized queries.

What it is

An UPDATE operation changes specific values in existing rows of a database table. Unlike INSERT, which adds new records, or DELETE, which removes them, UPDATE modifies data in place. The core mental model is: "Find the row(s) matching a condition, then change their columns."

Key terms include:

• SET clause: Defines which columns to change and their new values.
• WHERE clause: Specifies which rows to update. Omitting this updates every row.
• Parameterization: Using placeholders (like %s) instead of string concatenation to prevent SQL injection.

Why it matters

• Data Integrity: Allows users to correct mistakes or update status fields (e.g., marking an order as "shipped").
• Security: Proper use of parameters prevents malicious code from altering your query logic.
• Efficiency: Updates only necessary rows rather than deleting and re-inserting entire records.
• Auditability: Combined with timestamps, updates allow tracking when and how data changed.

Syntax or steps

The standard pattern for updating a record involves three parts: the SQL command, the parameters, and execution via a cursor.

1. Construct the SQL string with placeholders (%s for most DB drivers like psycopg2 or MySQL Connector).
2. Create a tuple containing the new values followed by the condition values.
3. Execute the command using cursor.execute().
4. Commit the transaction to save changes permanently.

Example

import sqlite3

# Connect to database
conn = sqlite3.connect('example.db')
cur = conn.cursor()

# Prepare the UPDATE statement
# Note: SQLite uses ? for placeholders, while PostgreSQL/MySQL often use %s
sql = "UPDATE users SET name = ?, email = ? WHERE id = ?"

# Define new values and the target ID
new_name = "Alice Smith"
new_email = "alice@example.com"
user_id = 101

# Execute with parameters as a tuple
cur.execute(sql, (new_name, new_email, user_id))

# Save changes
conn.commit()

# Verify the update
cur.execute("SELECT * FROM users WHERE id = ?", (user_id,))
print(cur.fetchone())

conn.close()

Part-by-part explanation:

• UPDATE users SET name = ?, email = ? WHERE id = ?: This tells the database to change the name and email columns for the row where id matches the third parameter.
• (new_name, new_email, user_id): These values are passed separately from the SQL string. The driver handles escaping and type conversion, ensuring safety.
• conn.commit(): Without this, the changes remain in memory and are lost when the connection closes.

Common mistakes

• Missing WHERE clause: Running UPDATE users SET active=0 without a filter disables all users. Always double-check filters.
• String formatting: Using f-strings like f"UPDATE ... WHERE id={id}" creates SQL injection vulnerabilities. Never do this.
• Forgetting commit: Changes appear to work during testing but vanish after restart if commit() is omitted.
• Wrong placeholder syntax: Mixing up %s (PostgreSQL/MySQL) and ? (SQLite). Check your specific database driver documentation.

When to use it

Use UPDATE when modifying existing data. Compare it with alternatives below:

Prefer UPDATE over DELETE + INSERT because it preserves primary keys and foreign key relationships intact.

Practice

Guided Exercise: Write a script that updates the status column to "completed" for all orders where total_price is greater than 500.

Challenge: Modify the previous exercise to return the number of rows affected. Hint: Use cur.rowcount after execution.

Solution Hint: cur.execute("UPDATE orders SET status='completed' WHERE total_price > ?", (500,)); print(cur.rowcount)

Quick check

Question: What happens if you execute an UPDATE statement without a WHERE clause?

Answer: Every row in the specified table will be updated with the new values, potentially causing massive data loss or corruption.

Summary

Updating records requires precise targeting via the WHERE clause and secure handling of inputs through parameterized queries. Always verify your filters and remember to commit transactions to persist changes.