SQL UPDATE Statement (2025)

By: Koen Verbeeck |Comments (1) | Related: > TSQL

Problem

We use databases to store information in tables. Once the data is written tothose tables, often the need arises to update certain fields during the lifetimeof the data. In the SQL language, we can use the UPDATE statement to achieve thisgoal. In this tutorial we cover several different examples of how to use theUPDATE statement.

Solution

In this tip, we'll show you how you can use theT-SQL UPDATE statement to update data in a database table. We'llbe using theAdventureWorks 2017 sample SQL database. If you want to follow along, you can downloadthe sample database and restore it on your system. You can copy paste the queriesand execute them on your machine using Microsoft SQL Server Management Studio (SSMS).

Simple UPDATE SQL Syntax

A simple UPDATE statement can take the following format:

UPDATE TableSET Column = <some expression>;

Update a Single Column

Let's look at fictitious example where [SickLeaveHours] is the column name with the INT data type in the [HumanResources].[Employee] table. The [SickLeaveHours] is set to 0, which is the set clause to update the existing records. Here is the sample Transact-SQL code:

UPDATE [HumanResources].[Employee]SET [SickLeaveHours] = 0;

This will set all the rows of the existing data for the column SickLeaveHours in the Employee tableto the value 0. You can also use an expression that references the column itself.In this example, we're enhancing the VacationHours column with 10%.

UPDATE [HumanResources].[Employee]SET [VacationHours] = [VacationHours] * 1.1;

When the UPDATE statement is executed, SQL Server will take the current valueof each row and multiply it with 1.1. The result will be stored in the column. Whenyou execute the statement in SSMS, the messages tab will display how many rows wereupdated by the statement:

Update Examples for Multiple Columns

You can also update multiple columns at once, by separating the columns witha comma. In this statement, we also updatethe ModifiedDate column of the table:

UPDATE [HumanResources].[Employee]SET [VacationHours] = [VacationHours] * 1.1 ,[ModifiedDate] = GETDATE();

It's possible to reference columns of the same table, while they are beingupdated as well. Let's say for example we are resetting all vacation hoursto zero. At the same time, the sick leave hours are updated based on the currentvalue of the vacation hours.

UPDATE [HumanResources].[Employee]SET [VacationHours] = 0 ,[SickLeaveHours] = IIF([VacationHours] < 10, 0, 25);

If SQL Server would update the VacationHours column first, you might assume thatthe SickLeaveHours column will also contain only zeroes, based on the expression(for more info about IIF and CASE, check out the tipSQL Server T-SQL CASE Examples).This is not the case though, the database engine updates both columns simultaneously,so the expression for the sick leave hours uses the values that were present inthe VacationHours column before the UPDATE statement.

Updating a Subset of Rows

In many cases, you don't want to update the entire table, but only certainrows. You can accomplish this by adding aWHERE clause to your UPDATE statement.

UPDATE myTableSET myColumn = <some expression>WHERE <Boolean expressions>;

Every row where the WHERE condition returns true will be updated; other rows willsimply be skipped. The principle is the same as a WHERE condition in a SELECT statement(for more info, see the tipSQL Server SELECT Examples). We can for example update the sick hours only ifthey're smaller than 10 (instead of using the IIF function as we did in theprevious example):

UPDATE [HumanResources].[Employee]SET [SickLeaveHours] = 0WHERE [SickLeaveHours] < 10;

In general, it's a best practice to restrict the rows you want to update,for performance reasons but also to avoid overwriting data that shouldn'thave been updated. When running an UPDATE query on a production server, you don'twant to forget your WHERE clause.

Updating a Single Table with Data from Multiple Tables

In the previous section, the new column values were either hardcoded or we usedexpressions on the columns of the table itself. It's also possible to usedata from other tables as well. We can do this by adding theFROM clause to the UPDATE statement. We can join the table we want to updatewith the other tables, and then we can use the columns of those other tables toupdate the original table. A skeleton query could look like this:

UPDATE t1SET myColumn = <some expression using columns from t1 and t2>FROM myTable t1
JOIN otherTable t2 ON t1.keycolumn = t2.keycolumnWHERE <Boolean expressions>;

The first table in the FROM clause is the table we want to update. The aliast1 is assigned to this table. Then we join it against another table, with the aliast2. What's important to notice is that after the UPDATE keyword, the aliast1 is used to indicate which table we want to update, instead of using the fulltable name.

In this example, we are giving our recently hired employees two extra days ofvacation. We can do this by joining the Employee table with the JobCandidate table.

UPDATE eSET [VacationHours] = e.[VacationHours] + 2FROM [HumanResources].[Employee] eJOIN [HumanResources].[JobCandidate] jc ON [jc].[BusinessEntityID] = [e].[BusinessEntityID];

No WHERE clause is needed, as the INNER JOIN already filters the data we needfor the query. In some cases, where the logic becomes more complex, you might needto resort to subqueries orcommon table expressions (CTE). For example, the following query returns anerror since you cannot directly aggregate in the SET list of the UPDATE statement.

UPDATE shSET [SubTotal] = SUM(sd.[UnitPrice] * sd.[OrderQty])FROM [Sales].[SalesOrderHeader] shJOIN [Sales].[SalesOrderDetail] sd ON [sd].[SalesOrderID] = [sh].[SalesOrderID]WHERE sh.[SalesOrderID] = 43659;

To work around this issue, a CTE can be used to pre-calculate the aggregate:

WITH CTE_agg AS( SELECT [SalesOrderID], Subtotal = SUM([UnitPrice] * [OrderQty]) FROM [Sales].[SalesOrderDetail] WHERE [SalesOrderID] = 43659 GROUP BY [SalesOrderID])UPDATE shSET [SubTotal] = c.[Subtotal]FROM [Sales].[SalesOrderHeader] shJOIN CTE_agg c ON c.[SalesOrderID] = [sh].[SalesOrderID];

Testing your UPDATE Statement

Since you'll be overwriting data, you might want to make sure the UPDATEstatement does what it is intended to do. There are two options for quick testing:

• Using a transaction
• Inserting a SELECT

Using a Transaction

With the first option, you surround the UPDATE query with an explicit transaction:

BEGIN TRAN;UPDATE TableSET Column = <some expression>WHERE <Boolean expressions>;COMMIT;
ROLLBACK;

You run the BEGIN TRAN statement along with the UPDATE statement. This will updatethe data in the table inside the current transaction context. You can check thedata in the table to verify everything is updated correctly. If this is the case,you can execute the COMMIT statement to commit the transaction or you can use theROLLBACK statement to roll back the transaction and undo the changes to the table.

Using the query from the previous example, we get this script:

BEGIN TRAN; WITH CTE_agg AS( SELECT [SalesOrderID], Subtotal = SUM([UnitPrice] * [OrderQty]) FROM [Sales].[SalesOrderDetail] WHERE [SalesOrderID] = 43659 GROUP BY [SalesOrderID])UPDATE shSET [SubTotal] = c.[Subtotal]FROM [Sales].[SalesOrderHeader] shJOIN CTE_agg c ON c.[SalesOrderID] = [sh].[SalesOrderID]; SELECT [SubTotal]FROM [Sales].[SalesOrderHeader]WHERE[SalesOrderID] = 43659; ROLLBACK;COMMIT;

To test this, we run this part first:

If the result is what we expect, the COMMIT statement can be executed, otherwisewe select ROLLBACK and execute that line of code. As long as neither of those statementsis run, the transaction is open and the changed data is not persisted. More infocan be found inWhat is a transaction?.

Using a Select Statement

The other option is to insert a SELECT into the UPDATE statement and "hide"it using comments. To test the query, we select only the relevant portion to seethe data that is going to be changed. For example:

UPDATE eSET [VacationHours] = e.[VacationHours] + 2-- SELECT *FROM [HumanResources].[Employee] eJOIN [HumanResources].[JobCandidate] jc ON [jc].[BusinessEntityID] = [e].[BusinessEntityID];

To test this query, we select everything right after the double dash and executeit:

With this trick, you can quickly inspect which rows are going to be updated,as long as the UPDATE statement has a FROM clause.

Updating a View

In all of the examples we have used physical tables to update. But SQL Serveralso allows you to update data through a view. There are a couple of prerequisitesto have an updateable view:

• The UPDATE statement can only reference columns from one base table. Thismeans it's not possible to update multiple tables at once using a singleUPDATE statement.
• The view columns that are modified must directly reference the data of thebase table. This means an aggregation function cannot be used, or any otherexpression using other columns. Using UNION (ALL) or other set operators arealso prohibited.
• There's no ORDER BY, GROUP BY, HAVING or DISTINCT.
• TOP is not used together with the WITH CHECK OPTION clause.

You can find more info on the prerequisites inthe documentation. To illustrate with an example, we're going to use theHumanResources.vEmployee view of the Adventure Works database. This viewhas the following definition which is based on a SELECT query:

CREATE VIEW [HumanResources].[vEmployee] AS SELECT e.[BusinessEntityID] ,p.[Title] ,p.[FirstName] ,p.[MiddleName] ,p.[LastName] ,p.[Suffix] ,e.[JobTitle] ,pp.[PhoneNumber] ,pnt.[Name] AS [PhoneNumberType] ,ea.[EmailAddress] ,p.[EmailPromotion] ,a.[AddressLine1] ,a.[AddressLine2] ,a.[City] ,sp.[Name] AS [StateProvinceName] ,a.[PostalCode] ,cr.[Name] AS [CountryRegionName] ,p.[AdditionalContactInfo]FROM [HumanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID] INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = e.[BusinessEntityID] INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID] INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID] INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode] LEFT OUTER JOIN [Person].[PersonPhone] pp ON pp.BusinessEntityID = p.[BusinessEntityID] LEFT OUTER JOIN [Person].[PhoneNumberType] pnt ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID] LEFT OUTER JOIN [Person].[EmailAddress] ea ON p.[BusinessEntityID] = ea.[BusinessEntityID];

As you can see, the view adheres to the prerequisites. This means we can runthe following UPDATE statement:

UPDATE [HumanResources].[vEmployee]SET [Title] = 'Testing'WHERE [BusinessEntityID] = 255;

We can indeed see the data has changed:

The prerequisites mention a single base table, but the view has several tablesjoined together using INNER JOINs. This means you can also update columns from othertables, such as the Person.Address table for example.

UPDATE Tips and Tricks

• If you want to update multiple tables at the same time – which isnot possible in a single UPDATE statement - and you want to make sure they allsucceed or fail together, you need to use explicit transactions.
• You can however update, delete and insert rows at the same time using theMERGE statement. The tipUsing MERGE in SQL Server to insert, update and delete at the same timeexplains the concept. However, the MERGE statement can be buggy, so make sureto readUse Caution with SQL Server's MERGE Statement as well.
• Running a large UPDATE statement can be a costly process. Not only doesSQL Server need to update the data, but also keep locks on the table and updatethe transaction log. To speed up large updates, you can use a batching strategy.The tipOptimize Large SQL Server Insert, Update and Delete Processes by Using Batchesprovides you with more detail. The tipUPDATE Statement Performance in SQL Server also gives more info about theperformance aspect.
• You can use@@rowcount to programmatically determine how many rows you've updated.
• You can use the UPDATE SQL statement at any location where you can run SQLscripts. So not only inSQL Server Management Studio, but also instored procedures,IntegrationServices,sqlcmd,PowerShell,Azure Data Factory,SQL Server Agent and so on.

Next Steps

• You can learn more about T-SQL DML code:
  • SELECT statementin this SQL tutorial
  • Learn aboutSQL DELETE code
  • INSERT INTO SQL Server Command
  • MERGE Logic
• The tipInsert and Delete SQL Server Data with Views uses the concept of updateableviews for inserting and deleting data from tables.
• The tipSQL Update Statement with Join in SQL Server vs Oracle vs PostgreSQL compareshow you can use the FROM clause in an UPDATE statement in different databaseengines.
• Learn about theSQLDROP TABLE andTRUNCATE TABLE commands.

About the author