Basic SQL UPDATE Statement with Examples

By: Jim Evans   |   Updated: 2022-08-29   |   Comments   |   Related: > TSQL

Problem

How do you change the values of data in a SQL Server table? How do I change a single column of a single record? How do I change a column value for all rows in a table? What is the best practice for running an ad hoc update statement in SQL? Check out this SQL tutorial to learn various ways to UPDATE data in SQL Server tables.

Solution

In this SQL tutorial, I will show examples of UPDATE statement syntax, demo a basic UPDATE of a single column for a single row, an UPDATE of a column for all rows, an UPDATE based on a join to a referencing table, and a multi-column UPDATE. I will introduce the OUTPUT CLAUSE to show before and after values and share a best practice example of basic transaction control to view your result before committing your change to the database. I will use Microsoft’s sample database AdventureWorks for this demo.

Basic SQL UPDATE Syntax

This is the basic SQL UPDATE syntax:

• Use the UPDATE command for the target table
• Select the column (or columns) we want to update with new values via the SET clause
• Optionally we can use a WHERE clause to limit the scope of the records updated

UPDATE schema.TableName
SET Col2 = ‘MSSQLTips’
WHERE Col1 = 1;

First - Set up a Test Table

First, create a test table called dbo.MySalesPerson and load data from other tables. This way we can run the UPDATE statements without messing up the original data in the AdventureWorks database.

Run the below setup in the AdventureWorks database to set up the sample table for these SQL queries.

--Set up Test data
USE [AdventureWorks]
GO

DROP TABLE IF EXISTS [dbo].[MySalesPerson];
GO

--A) Create Test Table
CREATE TABLE [dbo].[MySalesPerson](
   [BusinessEntityID] [int] NOT NULL,
   [TerritoryID] [int] NULL,
   [SalesQuota] [money] NULL,
   [Bonus] [money]  NULL,
   [CommissionPct] [smallmoney]  NULL,
   [SalesYTD] [money]  NULL,
   [SalesLastYear] [money]  NULL,
   [ModifiedDate] [datetime] NOT NULL,
);
GO
 
--B) Load Test Data from [Sales].[SalesPerson]
INSERT INTO [dbo].[MySalesPerson] 
   ([BusinessEntityID],[TerritoryID],[SalesQuota],[Bonus],[CommissionPct],[SalesYTD],[SalesLastYear],[ModifiedDate])
SELECT 
    [BusinessEntityID],[TerritoryID],[SalesQuota],[Bonus],[CommissionPct],[SalesYTD],[SalesLastYear],[ModifiedDate]
FROM [Sales].[SalesPerson];
GO
 
--C) Insert Additional Rows for Non- Sales Persons.
INSERT INTO [dbo].[MySalesPerson] 
   ([BusinessEntityID],[TerritoryID],[SalesQuota],[Bonus],[CommissionPct],[SalesYTD],[SalesLastYear],[ModifiedDate])
SELECT 
    e.BusinessEntityID 
   ,1 as [TerritoryID]
   ,NULL as [SalesQuota] -- Null value
   ,0 as [Bonus]
   ,1 as [CommissionPct]
   ,0 as [SalesYTD]
   ,0 as [SalesLastYear]
   ,Getdate() as [ModifiedDate]
FROM [HumanResources].[Employee] e
WHERE JobTitle = 'Database Administrator'; -- WHERE Condition
 
--D) Explore the Test Data.
SELECT *
FROM [dbo].[MySalesPerson];
GO

Example 1 – Basic SQL UPDATE Statement

This example shows how to perform a basic UPDATE statement with a WHERE clause controlling the record that is updated. A check query can be used to show that the TerritoryID for record with BusinessEntityID = 285 has been set to 1.

USE [AdventureWorks]
GO

--1) Update one column one row.
UPDATE [dbo].[MySalesPerson]
SET TerritoryID = 1
WHERE BusinessEntityID = 285;
 
--Show Results
SELECT BusinessEntityID, TerritoryID
FROM [dbo].[MySalesPerson]
WHERE BusinessEntityID = 285; -- WHERE Condition
GO

Example 1 Results:

Example 2 – Update All Rows with Caution

This example updates all rows and sets column SalesQuota to $30000 in the dbo.MySalesPerson table.

Note: There is no WHERE clause. It is important to ensure this is the intention. Often, users run an update forgetting to add a WHERE clause, unintentionally wiping out all the data in that column. Doing so will require a recovery of the table to get back the original values. This can result in a system outage in some cases.

--2) Update 1 column all rows
UPDATE [dbo].[MySalesPerson]
SET SalesQuota = 300000.00;
 
--Show Results
SELECT BusinessEntityID, SalesQuota
FROM [dbo].[MySalesPerson];
GO

Example 2 Results:

Example 3 – Update Clause with a Join Query

In this example, a JOIN query is used to control the rows to be updated. Here, I only change the SaleQuota to $50,000 in dbo.MySalesPerson WHERE the BusinessEntityID matches that of the HumanResources.Employee table and has a JobTitle of "Database Administrator". Run the second query to show the results of the UPDATE.

--3) Update from a Query
UPDATE [dbo].[MySalesPerson]
SET SalesQuota = 50000.00
FROM [dbo].[MySalesPerson] m
   INNER JOIN [HumanResources].[Employee] e ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Database Administrator';
 
--Show Results 
SELECT e.JobTitle, m.BusinessEntityID, m.SalesQuota
FROM [dbo].[MySalesPerson] m
   INNER JOIN [HumanResources].[Employee] e ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Database Administrator';
GO

Example 3 Results:

Example 4 – Update Multiple Columns with a SQL Query

In this example, we expand to update multiple columns, setting the values to those from a joined table.

--4) Update Multiple columns from a Query
 
UPDATE [dbo].[MySalesPerson]
SET [TerritoryID] = s.[TerritoryID]
   ,[SalesQuota] = s.[SalesQuota]
   ,[Bonus] = s.[Bonus]
   ,[CommissionPct] = s.[CommissionPct]
   ,[SalesYTD] = s.[SalesYTD]
   ,[SalesLastYear] = s.[SalesLastYear]
   ,[ModifiedDate] = Getdate()
FROM [dbo].[MySalesPerson] m
   INNER Join [Sales].[SalesPerson] s ON s.BusinessEntityID = m.BusinessEntityID;
 
--Show new results
SELECT m.*
FROM [dbo].[MySalesPerson] m
   INNER Join [Sales].[SalesPerson] s ON s.BusinessEntityID = m.BusinessEntityID;
GO

Example 4 Results:

Example 5 – Update Statement with an OUTPUT Clause

The following UPDATE statement introduces the OUTPUT clause to show the before and after results in a single update statement.

 --5) Update with Output clause showing before and after values
UPDATE [dbo].[MySalesPerson]
SET TerritoryID = 5,
    SalesQuota = 50000.00
OUTPUT INSERTED.BusinessEntityID, 
       DELETED.TerritoryID as [Before Terr], 
       INSERTED.TerritoryID as [After Terr],
       DELETED.SalesQuota as [Before SQ], 
       INSERTED.SalesQuota as [After SQ]
FROM [dbo].[MySalesPerson] m
WHERE m.BusinessEntityID in (270,271);
GO

Example 5 Results show the before and after values from the OUTPUT clause.

Example 6 – Best Practice using Transaction Control and OUTPUT Clause

When doing ad hoc data updates, a recommended best practice is to use Transaction Control and the OUTPUT clause.

Below is a template for ad hoc updates that include Transaction Control and the OUTPUT clause. Using this, you can validate the results by reviewing the Before and After output values and then choose to COMMIT the transaction if the output is expected or ROLLBACK the transaction if the results are not as expected.

--1) Run the UPDATE in a Transaction with Output clause
BEGIN TRAN ;
UPDATE schema.TableName
SET Col2 = value
OUTPUT DELETED.Col2 as [before], INSERTED.Col2 as [after]
FROM schema.TableName
WHERE Col1 = 1;
 
--2) Run ROLLBACK if the output IS NOT expected
ROLLBACK;
--Or Run COMMIT if output is expected 
COMMIT;

The query below is based on the template above. Only run the first part of the query, not the ROLLBACK or COMMIT statements. Review the results and then run the ROLLBACK or COMMIT statements.

Note: In most query tools, you can just select the code you want to run and then run the query and only the highlighted portion will run.

--6) Best Practices using Transaction Control and Output Clause
-- just select this section of code to run
BEGIN TRAN
UPDATE [dbo].[MySalesPerson]
SET TerritoryID = 1,
   SalesQuota = 30000.00
OUTPUT 
   INSERTED.BusinessEntityID, 
   DELETED.TerritoryID as [Before Terr], 
   INSERTED.TerritoryID as [After Terr],
   DELETED.SalesQuota as [Before SQ], 
   INSERTED.SalesQuota as [After SQ]
FROM [dbo].[MySalesPerson] m
   INNER JOIN [HumanResources].[Employee] e ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Database Administrator';
-- run the query just to here!

 
-- Then, Undo the Update if Results are not what is expected
ROLLBACK;
--OR Commit the Update if the results look good.
COMMIT;
GO

Here are the results before committing or rolling back the transaction. Be sure to run the second part, either commit or rollback to complete the transaction!

Wrap Up

This SQL tutorial provides examples of SQL UPDATE queries, shares tips on showing the before and after results, and recommends a best practice technique when doing ad hoc updates in a production environment. Be cautious and take care when doing data manipulation in SQL. It is easy to make a mistake that requires recovery or causes a system outage. For ad hoc updates, use transaction control and check the results before committing the transaction. Below are other references to basic SQL data manipulation articles.

Next Steps

• See other basic SQL statement examples:
  • How to Write a SQL SELECT Statement
  • Basic SQL INSERT Statement with Examples
  • Basic SQL DELETE Statement with Examples
• See this quick reference on different SQL JOIN Types with Examples
• Learn more about SQL Transaction Control Statements
• Learn about Subqueries:
  • SQL Server Subquery Example
  • Introduction to Subqueries in SQL Server
• Reference: SQL Update Statement with Join in SQL Server vs Oracle vs PostgreSQL

About the author

Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips