Updating records in a database is a core operation in nearly every application. Whether you’re modifying a user’s profile, adjusting inventory, or fixing data errors, the SQL UPDATE statement helps you modify existing records. In this article, you’ll learn how to use UPDATE queries with Microsoft SQL Server (MSSQL) using Python and the pyodbc library.

✅ Prerequisites

Tools Required

• Python 3.7+

• Microsoft SQL Server (Express, Developer, or Full)

• ODBC Driver 17 or 18 for SQL Server

Python Library

If you haven’t already installed pyodbc, do so with:

pip install pyodbc

Sample Table

We'll use a sample table called Employees:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    Age INT,
    Salary DECIMAL(10, 2),
    IsActive BIT
);

Step 1: Connect to the MSSQL Server

import pyodbc

conn = pyodbc.connect(
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=localhost;"
    "DATABASE=EmployeeDB;"
    "UID=your_username;"
    "PWD=your_password;"
)
cursor = conn.cursor()

✏️ Step 2: Update a Single Column

Example: Update Email of a Specific Employee

employee_id = 101
new_email = "[email protected]"

cursor.execute(
    "UPDATE Employees SET Email = ? WHERE EmployeeID = ?",
    (new_email, employee_id)
)
conn.commit()
print("Email updated.")

✅ Use parameterized queries to avoid SQL injection.

Step 3: Update Multiple Columns

Example: Update Salary and Age

cursor.execute("""
    UPDATE Employees
    SET Salary = ?, Age = ?
    WHERE EmployeeID = ?
""", (65000.00, 30, 101))

conn.commit()
print("Salary and age updated.")

Step 4: Update Based on Conditions

Example: Give a Raise to All Active Employees

cursor.execute("""
    UPDATE Employees
    SET Salary = Salary + 5000
    WHERE IsActive = 1
""")
conn.commit()
print("Salary updated for all active employees.")

Full Working Example

import pyodbc

def update_employee_salary(emp_id, new_salary):
    try:
        conn = pyodbc.connect(
            "DRIVER={ODBC Driver 17 for SQL Server};"
            "SERVER=localhost;"
            "DATABASE=EmployeeDB;"
            "UID=your_username;"
            "PWD=your_password;"
        )
        cursor = conn.cursor()
        
        cursor.execute(
            "UPDATE Employees SET Salary = ? WHERE EmployeeID = ?",
            (new_salary, emp_id)
        )
        conn.commit()

        if cursor.rowcount > 0:
            print(f"Employee {emp_id}'s salary updated to ${new_salary}.")
        else:
            print(f"No employee found with ID {emp_id}.")

    except pyodbc.Error as e:
        print("Error updating employee:", e)
    finally:
        cursor.close()
        conn.close()

# Usage
update_employee_salary(101, 72000.00)

Tips for Using UPDATE in Python with MSSQL

⚠️ Common Pitfalls

Best Practices

• Test your UPDATE with a SELECT first to preview affected rows.

• Use transactions if you're updating multiple tables or rows.

• For bulk updates, consider writing a stored procedure and calling it from Python.

• Log all update actions for auditability in sensitive systems.

Conclusion

The UPDATE statement is a powerful tool in your database toolkit. With Python and pyodbc, you can safely and efficiently modify MSSQL records. Just remember: use parameterized queries, always include a WHERE clause, and commit your changes to avoid surprises.