The UPDATE statement in SQL Server is used to modify existing records in a table. It allows you to change the values of one or more columns in one or multiple rows based on specified conditions.

Syntax

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

Key Components

table_name: The name of the table where the data will be updated.

SET: Specifies the columns and their new values.

column1 = value1: Assigns a new value (value1) to the specified column (column1).

WHERE condition: Specifies which rows should be updated. Without a WHERE clause, all rows in the table are updated.

value: Can be a constant, an expression, or a subquery.

Examples

Updating a Single Row

Suppose you have a Customers table with the following columns: CustomerID, Name, City.

To update the city of a customer with CustomerID = 1:

UPDATE Customers
SET City = 'New York'
WHERE CustomerID = 1;

Updating Multiple Rows

To change the city to ‘Los Angeles’ for all customers currently in ‘San Francisco’:

UPDATE Customers
SET City = 'Los Angeles'
WHERE City = 'San Francisco';

Using Expressions

To increase the salary of all employees by 10% in an Employees table:

UPDATE Employees
SET Salary = Salary * 1.10;

Omitting the WHERE Clause

If the WHERE clause is omitted, the UPDATE statement will affect all rows in the table. For example:

UPDATE Customers
SET City = 'Unknown';

This will set the City column to ‘Unknown’ for every customer in the table.

Updating Using a Subquery

You can use a subquery to dynamically determine the new value. For example:

UPDATE Products
SET Price = Price * 0.9
WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName = 'Electronics');

Transactions and Rollback

To ensure data integrity, it’s good practice to use transactions when executing an UPDATE statement, especially if it affects multiple rows:

BEGIN TRANSACTION;

UPDATE Orders
SET Status = 'Shipped'
WHERE OrderDate < '2024-01-01';

-- If something goes wrong
ROLLBACK TRANSACTION;

-- Otherwise, commit changes
COMMIT TRANSACTION;

Common Use Cases

Correcting data errors.
Applying adjustments or updates based on new business rules.
Synchronizing data between tables.

Performance Considerations

Use indexes to improve the performance of the WHERE clause.
Minimize the number of rows updated by narrowing down conditions in the WHERE clause.
Avoid updating large datasets in one operation; consider batching updates if the table is huge.
By understanding the proper syntax and careful usage of the UPDATE statement, you can effectively and safely modify your SQL Server database records.