T-SQL Programming Part 7 – Understanding How to Use the UPDATE Statement to Change Your Data

If you are an application programmer then you will more than likely need to write TSQL code to update your SQL Server database tables.  In order to update a row in a SQL Server table you will use the UDPATE statement.  In this article I will show you a number of different ways to use the UPDATE statement to modify the data in your SQL Server tables.

Basic Syntax of the Update Statement

There are multiple ways to use the UPDATE statement to update a SQL Server table.  In this article I will show you the most common methods of using the UPDATE statement.  Below is the basic syntax for the UPDATE statement:

UPDATE 
    [ TOP ( expression ) [ PERCENT ] ]
    <object_name> 
    SET <column_name> = <value> [ ,…n]
    [ <OUTPUT Clause> ]
    [ FROM <table_source> [ ,…n]
    [ WHERE  <search_condition> 

Where:

                 <expression> –  an a expression that evaluates to a number that will be either the number or a percentage of rows to update.

                 <object_name> – is the name of the table that will be updated

                 <column_name> – is the name of the column, or set of columns that will be updated

                 <value> – is the value of the string or number that will be used to update the <column_name>

                 <table_source> – is the name of the table, view or derived table that provides the values that will be used to update a <column_name>

                 <search_condition> – defines the condition or conditions that will be used to retrieve rows from the <table_source>

This is not the complete syntax of the update statement.  If you want to review the complete syntax of the UPDATE statement then please refer to Books Online.

Setting Up Some Test Data

To properly show you how to use the UPDATE statement I will need to create a few tables to hold some sample data.  The first table I create will hold a list of Toys.  Below is the code to create my Toy table:

SET NOCOUNT ON;
USE tempdb;
go
CREATE TABLE Toy (
       ID int identity not null, 
       ToyName varchar(25) not null, 
       Price decimal(6,2) not null
       );
INSERT INTO Toy
       VALUES ('MagicWnd',10.58),
              ('BusyMan',29.71),
              ('SilverMagic',2.99),
              ('SuperSurfer',15.81);

In order to show you how to UPDATE a table from data in another table I need to build a second sample data table that I will call NewToyPrice.  Below is the TSQL code to create and populate this table.

SET NOCOUNT ON;
USE tempdb;
go
CREATE TABLE NewToyPrice (      
       ID int not null, 
       ToyName varchar(25) not null, 
       Price decimal(6,2) not null
       );
INSERT INTO NewToyPrice 
       VALUES (1, 'Magic Wand',10.99),
              (2, 'Busy Man',29.99),
              (3, 'Silver Magic',3.27),
              (4, 'Super Surfer',16.19);

These two different tables will be used in my UDPATE statement examples below.

How to Update a Single Column on a Single Row

There may be times when you need to write some code to update a row, or set of rows in a table.  This is when you need to know how to use the UPDATE statement to update those rows. 

In my first TSQL script above, when I create the TOY table, there is typo in the first ToyName.  I created a ToyName of “MagicWnd” when it should have been “MagicWand”.  To update this single row I can run the following UPDATE statement:

UPDATE Toy 
  SET ToyName = 'MagicWand'
WHERE ToyName = 'MagicWnd';

In this UPDATE statement I found the row with the misspelled ToyName using the update <search condition>.  The <search condition> is right after the WHERE clause, which I specified the condition ToyName = ‘MagicWnd’.  By using this <search condition> the UPDATE statement was able to find the one row in my Toy table that had the misspelled ToyName.  To update the row that was found I used the “SET” clause of the UPDATE statement, which set the new ToyName to the correct spelling of “MagicWand”.  

Issues with Using the Update Statement

You need to be careful when using the UPDATE statement with a <search condition>.  If you incorrectly specify your search condition you might update too many rows, or not enough rows.  Additionally if you forget the WHERE clause altogether, you will update the entire table when you might not intend to. 

Here is an example where I specified the WHERE clause incorrectly and I updated too many rows:

UPDATE Toy 
  SET ToyName = 'Silver Magic'
WHERE ToyName like 'S%';

Here I updated ToyName on two rows, the “SilverMagic” and the “SuperSurfer” Toyname rows.

If you are not exactly sure what rows will be returned by your WHERE clause of an UPDATE statement, it best to take some precautions prior to executing your UPDATE statement. What I like to do is to first execute a SELECT statement that contains the <search condition> I plan to use in my WHERE clause of my UPDATE statement.  By doing this I can see what my WHERE condition will return to make sure it identifies the same rows I want to update.  Once my SELECT statement does return the correct rows I can then copy the WHERE clause from my SELECT statement, and paste it into my UDPATE statement code.  By doing this I have made sure I don’t have a bogus WHERE clause on my UPDATE statement that is going to incorrectly identify rows to be updated in my table.

Updating Multiple Columns

In my examples above I only updated a single column with my UPDATE statement.  Suppose I wanted to change both the ToyName and the Price columns.  I can update multiple columns with a single UPDATE statement.  Below is an example that does this:

UPDATE Toy 
  SET ToyName = 'MagicSurfer', 
      Price = 15.99
WHERE ID = 4;

I updated the ToyName and the Price for the Toy row with an ID value of 4, which was my original SuperSurfer row.  I did this by having a single SET clause in my UPDATE statement, with the two column name/value pairs separated with a comma.  

Updating a Table Based on Values in a Different Table

There may be times when you don’t want to manually write a bunch of UPDATE statements with different literal strings to update your table.  Suppose I wanted to change all the prices of my Toys with a single UPDATE statement. I can do that with the following code:

UPDATE Toy  
   SET ToyName = N.ToyName, 
       Price = N.Price
FROM Toy T JOIN NewToyPrice N
     ON T.ID = N.ID;
 

In this code I updated the Toy table based on values in another table, in this case a table named NewToyPrice.  To accomplish that I joined the TOY table to my NewToyPrice table based on the ID column.  I then used the NewToyPrice column values for ToyName and Price to update my Toy table column values on rows that have matching column ID values.

Limiting the Rows that get Updated Using Top Clause

We have already seen how to limit the rows being updated by using the WHERE clause.  You can also limit the rows being updated using the TOP clause.  Suppose you want to update only two rows with a new price then you can run the following code:  

UPDATE TOP (2) Toy 
SET Price =   29.99

This code updates two random rows in my Toy table. I say random rows because TSQL doesn’t guarantee order unless you have an order by clause.  If you want to update two rows based on the ORDER BY clause then you will need to use the TOP clause in a sub query in conjunction with a WHERE constraint, like in the following example.

UPDATE Toy  
SET Price =   99.99
FROM (SELECT TOP 2 ID as TopID 
      FROM Toy ORDER BY Price) A
WHERE ID = A.TopID;

In this example I have a sub query that selects the top two rows based on Price.  This sub query returns a ToyID column value that is then used in the WHERE constraint to match up with the Toy table ID column, to identify the rows in the Toy table that should have their Price updated.

Understanding the UPDATE Statement

In this article I provided you a number of different examples of how to use the update statement.   By using these examples you should be able to update a single column or multiple columns by either a specific value like a literal string, or from values in another table. Understanding the UPDATE statement and how to use it is important for maintaining your database table data.

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles