Basic SQL UPDATE Statement with Examples

By:   |   Updated: 2022-08-29   |   Comments   |   Related: More > 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:

Basic UPDATE statement 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:

Update all rows with caution 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:

Update with a Join query 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:

Update multiple columns with a query 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.

Update with an OUTPUT clause results

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!

Best practices using transaction control and output clause results

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



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



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

View all my tips


Article Last Updated: 2022-08-29

Comments For This Article

















get free sql tips
agree to terms