SQL UPDATE Statement (T-SQL)

The UPDATE statement in T-SQL is used to modify existing records in a table. It allows you to change the values of one or more columns for specific rows.

Basic Syntax

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition;

Explanation of Components:

UPDATE table_name: Specifies the table you want to update.
SET column1 = value1, column2 = value2, ...: Defines the columns to update and the new values they should take. You can update multiple columns in a single UPDATE statement by separating them with commas.
WHERE condition: This clause is crucial. It filters the rows that will be updated. If you omit the WHERE clause, all rows in the table will be updated, which is usually not desired and can lead to data loss or corruption.

Example 1: Updating a Single Column

Suppose we have a Customers table and we want to update the email address for a customer with CustomerID 101.

UPDATE Customers
SET Email = 'new.email@example.com'
WHERE CustomerID = 101;

Example 2: Updating Multiple Columns

Let's update both the City and Country for the same customer.

UPDATE Customers
SET City = 'Metropolis',
    Country = 'New Continent'
WHERE CustomerID = 101;

Note: It's highly recommended to run a SELECT statement with the same WHERE clause before executing an UPDATE statement to verify which rows will be affected.

Example 3: Updating Based on Another Table (JOIN)

You can update a table based on values from another related table using a JOIN in T-SQL.

UPDATE c
SET c.Region = o.RegionName
FROM Customers AS c
INNER JOIN Orders AS o
    ON c.CustomerID = o.CustomerID
WHERE o.OrderID = 500;

In this example, the Region in the Customers table is updated with the RegionName from the Orders table for a specific OrderID.

Important Considerations:

Data Integrity: Always ensure the values you are updating conform to the column's data type and any constraints (like UNIQUE, CHECK, or FOREIGN KEY constraints).
Transaction Management: For critical updates, consider wrapping your UPDATE statement within a transaction (BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION) to allow for error handling and rollback if something goes wrong.
Performance: Updating a large number of rows can impact performance. Ensure your WHERE clause is efficient and uses indexed columns where possible.
Backups: Always have recent backups of your database before performing significant data modifications.

Updating with NULL Values

You can set a column to NULL if the column allows nulls:

UPDATE Products
SET Description = NULL
WHERE ProductID = 25;

Updating with Expressions

You can update columns using expressions or calculations:

UPDATE Employees
SET Salary = Salary * 1.05 -- 5% raise
WHERE Department = 'Sales';