How to perform an UPDATE in SQL Server

Written by Johannes Åkesson
Reviewed by Johannes Åkesson
Jun 28, 2024
3 min read

Updating data in SQL Server is a common task for business users who need to ensure their data remains accurate and up-to-date. This guide will walk you through the process of using the ‘UPDATE’ statement in SQL Server, providing easy-to-follow steps and code snippets you can use directly in SQL Server Management Studio (SSMS).

Understanding the SQL UPDATE statement

The UPDATE statement in SQL Server is used to modify the existing records in a table. You can update one or multiple columns at a time based on certain conditions. The basic syntax for the ‘UPDATE’ statement is:

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

Steps to perform a SQL UPDATE

  1. Identify the Table and Columns to Update: Determine which table and columns you need to update. Ensure you have the necessary permissions to update the data.
  2. Write the UPDATE Statement: Formulate the ‘UPDATE’ statement with the appropriate ‘SET’ and ‘WHERE’ clauses.
  3. Execute the Statement: Run the statement in SSMS to apply the changes.

Example 1: Updating a single column

Let’s start with a simple example where we update a single column. Suppose we have a table named ‘Employees’ and we want to update the ‘LastName’ of an employee with ‘EmployeeID’ of 1.

UPDATE Employees
SET LastName = 'Smith'
WHERE EmployeeID = 1;

Example 2: Updating multiple columns

In the next example, we will update multiple columns. Assume we want to update the ‘FirstName’ and ‘LastName’ of the same employee.

UPDATE Employees
SET FirstName = 'John', LastName = 'Smith'
WHERE EmployeeID = 1;

Example 3: Updating based on a condition

Sometimes, you may need to update records based on specific conditions. For instance, if you want to give a 10% salary raise to all employees in the ‘Sales’ department, you would do the following:

UPDATE Employees
SET Salary = Salary * 1.10
WHERE Department = 'Sales';

Example 4: Using a Subquery in an UPDATE Statement

You can also use a subquery to update a table based on values from another table. Suppose we have a table ‘Departments’ and we want to update the ‘ManagerID’ in the ‘Employees’ table to the ‘ManagerID’ from the ‘Departments’ table where the department names match.

UPDATE Employees
SET ManagerID = (SELECT ManagerID FROM Departments WHERE
Departments.DepartmentName = Employees.Department)
WHERE Department IS NOT NULL;

Best practices for using UPDATE statements

  • Always Backup Your Data: Before performing any update operations, ensure you have a backup of your data to prevent accidental data loss.
  • Test Your Queries: Run your queries in a test environment to make sure they perform as expected without unintended side effects.
  • Use Transactions: For critical updates, use transactions to ensure that your updates are atomic and can be rolled back if something goes wrong.
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = Salary * 1.10
WHERE Department = 'Sales';

-- If everything is correct
COMMIT TRANSACTION;

-- If something went wrong
-- ROLLBACK TRANSACTION;
  • Be cautious with ‘WHERE’: Be cautious with ‘WHERE’ clauses to avoid updating all rows unintentionally with a condition like ‘WHERE’ 1=1. Always double-check your conditions.

Conclusion

Updating data in SQL Server is straightforward once you understand the syntax and best practices. By following the examples provided, you can confidently perform updates in your database. Remember to always backup your data and test your queries before executing them in a production
environment.

For more tips and detailed guides on SQL Server, visit our SQL Spreads blog.

Leave a Reply


No comments yet. Be the first!

Try SQL Spreads for free

Try SQL Spreads during 14 days for free. The free trial is the fully
functional, time-limited only product.
Try Excel Add-In for Free download-image
stars-image
14-days Free Trial. No credit card required.