UPDATE Basics in SQL Server

Comments 8

Share to social media

In most cases, when using Transact-SQL to modify data in a SQL Server database, you issue an UPDATE statement that changes specific values. You can issue an UPDATE statement against a table or updateable view, as long as the statement modifies data in only one base table at a time.

By using an UPDATE statement, you can modify data in individual rows, sets of rows, or all rows in a table. An UPDATE statement must always include a SET clause, which identifies the columns to be updated. In addition, the statement can include a WHERE clause, which determines what rows to modify, or a FROM clause, which identifies tables or views that provide values for the expressions defined in the SET clause.

In this article, I discuss how to use the UPDATE statement to modify data. I also provide examples that demonstrate how the various clauses work. I created the examples in the AdventureWorks2008 database on a local instance of SQL Server 2008. However, most of the examples will work in the original AdventureWorks database, on SQL Server 2005 or 2008. Note, however, that the data is slightly different in the AdventureWorks database from what’s stored in the AdventureWorks2008 database, so your results will be different than what is shown here. Otherwise, most of the examples will run fine. (Only the last example might need to be modified, which I’ll explain when we get to it.)

To try out the examples in the article, you must first create the SalesStaff table, as shown in the following Transact-SQL code:

Notice that I tag on a SELECT statement at the end of the code to retrieve the new content in the SalesStaff table. (I also add a SELECT statement to the examples to verify the data modifications.) The SELECT statement above returns the results shown in the following table:

FullName

TerritoryName

TerritoryGroup

SalesQuota

SalesYTD

SalesLastYear

Stephen Jiang

NULL

NULL

NULL

677558.4653

0.00

Michael Blythe

Northeast

North America

300000.00

4557045.0459

1750406.4785

Linda Mitchell

Southwest

North America

250000.00

5200475.2313

1439156.0291

Jillian Carson

Central

North America

250000.00

3857163.6332

1997186.2037

Garrett Vargas

Canada

North America

250000.00

1764938.9859

1620276.8966

Tsvi Reiter

Southeast

North America

300000.00

2811012.7151

1849640.9418

Pamela Ansman-Wolfe

Northwest

North America

250000.00

0.00

1927059.178

Shu Ito

Southwest

North America

250000.00

3018725.4858

2073505.9999

José Saraiva

Canada

North America

250000.00

3189356.2465

2038234.6549

David Campbell

Northwest

North America

250000.00

3587378.4257

1371635.3158

Tete Mensa-Annan

Northwest

North America

300000.00

1931620.1835

0.00

Syed Abbas

NULL

NULL

NULL

219088.8836

0.00

Lynn Tsoflias

Australia

Pacific

250000.00

1758385.926

2278548.9776

Amy Alberts

NULL

NULL

NULL

636440.251

0.00

Rachel Valdez

Germany

Europe

250000.00

2241204.0424

1307949.7917

Jae Pak

United Kingdom

Europe

250000.00

5015682.3752

1635823.3967

Ranjit Varkey Chudukatil

France

Europe

250000.00

3827950.238

2396539.7601

Once you’ve created the SalesStaff table, you’re ready to try out the examples. So let’s look at how to create a basic UPDATE statement.

NOTE: Except for the last example, the examples in the article build on each other, so the results shown are based on the assumption that you’ll run each example and do so in consecutive order.

Defining a Basic UPDATE Statement

As I mentioned above, your UPDATE statement must include a SET clause. The clause identifies which columns in the target table should be modified and what the new values should be. The following example modifies the data in the SalesStaff table by changing the value of the SalesQuota column to 250000.

Notice that I first specify the UPDATE keyword, followed by the name of the table-SalesStaff. When specifying the table or view, be sure to qualify the name with the schema, database, and server names, as necessary.

After I specify the table, I define the SET clause. In most cases, the clause will include one or more column/value pairs defined according to the following syntax:

In the example above, <column> is SalesQuota and <value> is 250000. When you run the statement, the SalesQuota value is updated for every row in the SalesStaff table, as shown in the following results:

FullName

TerritoryName

TerritoryGroup

SalesQuota

SalesYTD

SalesLastYear

Stephen Jiang

NULL

NULL

250000.00

677558.4653

0.00

Michael Blythe

Northeast

North America

250000.00

4557045.0459

1750406.4785

Linda Mitchell

Southwest

North America

250000.00

5200475.2313

1439156.0291

Jillian Carson

Central

North America

250000.00

3857163.6332

1997186.2037

Garrett Vargas

Canada

North America

250000.00

1764938.9859

1620276.8966

Tsvi Reiter

Southeast

North America

250000.00

2811012.7151

1849640.9418

Pamela Ansman-Wolfe

Northwest

North America

250000.00

0.00

1927059.178

Shu Ito

Southwest

North America

250000.00

3018725.4858

2073505.9999

José Saraiva

Canada

North America

250000.00

3189356.2465

2038234.6549

David Campbell

Northwest

North America

250000.00

3587378.4257

1371635.3158

Tete Mensa-Annan

Northwest

North America

250000.00

1931620.1835

0.00

Syed Abbas

NULL

NULL

250000.00

219088.8836

0.00

Lynn Tsoflias

Australia

Pacific

250000.00

1758385.926

2278548.9776

Amy Alberts

NULL

NULL

250000.00

636440.251

0.00

Rachel Valdez

Germany

Europe

250000.00

2241204.0424

1307949.7917

Jae Pak

United Kingdom

Europe

250000.00

5015682.3752

1635823.3967

Ranjit Varkey Chudukatil

France

Europe

250000.00

3827950.238

2396539.7601

As you can see, all SalesQuota values have been updated to 250000. The <value> expression is simply the literal value to be inserted into the column. However, the <value> expression can be more complex, and often is. For example, in the following UPDATE statement, I add 50000 to the existing SalesQuota value:

Notice that the <value> expression includes the column name, the plus sign, and the value of 50000. As a result, the amount of 50000 is added to the existing value, as shown in the following results:

FullName

TerritoryName

TerritoryGroup

SalesQuota

SalesYTD

SalesLastYear

Stephen Jiang

NULL

NULL

300000.00

677558.4653

0.00

Michael Blythe

Northeast

North America

300000.00

4557045.0459

1750406.4785

Linda Mitchell

Southwest

North America

300000.00

5200475.2313

1439156.0291

Jillian Carson

Central

North America

300000.00

3857163.6332

1997186.2037

Garrett Vargas

Canada

North America

300000.00

1764938.9859

1620276.8966

Tsvi Reiter

Southeast

North America

300000.00

2811012.7151

1849640.9418

Pamela Ansman-Wolfe

Northwest

North America

300000.00

0.00

1927059.178

Shu Ito

Southwest

North America

300000.00

3018725.4858

2073505.9999

José Saraiva

Canada

North America

300000.00

3189356.2465

2038234.6549

David Campbell

Northwest

North America

300000.00

3587378.4257

1371635.3158

Tete Mensa-Annan

Northwest

North America

300000.00

1931620.1835

0.00

Syed Abbas

NULL

NULL

300000.00

219088.8836

0.00

Lynn Tsoflias

Australia

Pacific

300000.00

1758385.926

2278548.9776

Amy Alberts

NULL

NULL

300000.00

636440.251

0.00

Rachel Valdez

Germany

Europe

300000.00

2241204.0424

1307949.7917

Jae Pak

United Kingdom

Europe

300000.00

5015682.3752

1635823.3967

Ranjit Varkey Chudukatil

France

Europe

300000.00

3827950.238

2396539.7601

The SET clause can also specify more than one column/value set. If you include multiple sets, you simply separate the sets with commas. For instance, the following example updates the SalesQuota, SalesYTD, and SalesLastYear columns:

As you can see, I’ve defined a <value> expression for each column/value pair. For the SalesQuota column, I increase the value by 50000. For the SalesYTD column, I simply specify a literal value, which in this case is 0. For the SalesLastYear column, I increase the value by 5% by multiplying the column by 1.05. The following table shows the results returned by the SELECT statement after I update the three columns:

FullName

TerritoryName

TerritoryGroup

SalesQuota

SalesYTD

SalesLastYear

Stephen Jiang

NULL

NULL

350000.00

0.00

0.00

Michael Blythe

Northeast

North America

350000.00

0.00

1837926.8024

Linda Mitchell

Southwest

North America

350000.00

0.00

1511113.8306

Jillian Carson

Central

North America

350000.00

0.00

2097045.5139

Garrett Vargas

Canada

North America

350000.00

0.00

1701290.7414

Tsvi Reiter

Southeast

North America

350000.00

0.00

1942122.9889

Pamela Ansman-Wolfe

Northwest

North America

350000.00

0.00

2023412.1369

Shu Ito

Southwest

North America

350000.00

0.00

2177181.2999

José Saraiva

Canada

North America

350000.00

0.00

2140146.3876

David Campbell

Northwest

North America

350000.00

0.00

1440217.0816

Tete Mensa-Annan

Northwest

North America

350000.00

0.00

0.00

Syed Abbas

NULL

NULL

350000.00

0.00

0.00

Lynn Tsoflias

Australia

Pacific

350000.00

0.00

2392476.4265

Amy Alberts

NULL

NULL

350000.00

0.00

0.00

Rachel Valdez

Germany

Europe

350000.00

0.00

1373347.2813

Jae Pak

United Kingdom

Europe

350000.00

0.00

1717614.5665

Ranjit Varkey Chudukatil

France

Europe

350000.00

0.00

2516366.7481

As you can see, updating column values is a very straightforward process when using an UPDATE statement. However, all the examples we’ve looked at so far have each updated the entire table. However, you’ll often want to update only specific rows. So let’s look at how that is done.

Using a WHERE Clause to Qualify an UPDATE Statement

To limit the rows that are updated when you issue an UPDATE statement, add a WHERE clause after the SET clause. The WHERE clause specifies the search conditions that define which rows in the target table should be updated.

In the following UPDATE statement, I modify only the rows that have a TerritoryName value of United Kingdom:

As you can see, I specify in the SET clause that the TerritoryName value should be changed to UK. However, because the WHERE clause is also included, only the rows that currently have a value of United Kingdom are changed, which in this case, is only one row. The following table shows the results from the SELECT statement after the changes have been made:

FullName

TerritoryName

TerritoryGroup

SalesQuota

SalesYTD

SalesLastYear

Jae Pak

UK

Europe

350000.00

0.00

1717614.5665

In the previous example, the same column is specified in both the SET and WHERE clauses. However, the WHERE clause is not restricted in this way. For example, in the following example, I update the territory name for any row whose FullName value is Jae Pak:

Basically, I’m undoing the change I made in the previous statement by assigning the United Kingdom value to the TerritoryName column. However, only the Jae Pak row is modified. The following table shows the updated rows returned by the SELECT statement:

FullName

TerritoryName

TerritoryGroup

SalesQuota

SalesYTD

SalesLastYear

Jae Pak

United Kingdom

Europe

350000.00

0.00

1717614.5665

As the examples demonstrate, the WHERE clause determines only which rows should be updated, whereas the SET clause is concerned only with updating column values. In that sense, the two clauses are independent of each other. That means that you can be as specific in one clause as necessary. For example, in the following UPDATE statement, I modify the row with the FullName value of Stephen Jiang:

As you can see, the WHERE clause limits the rows being updated to those that include the FullName value of Stephen Jiang (again, only one row). However, the SET clause specifies that several columns be modified. The first two columns modified are TerritoryGroup and SalesQuota. You’ve seen both of these types of column/value pairs in previous examples.

But the <value> expression matched up to the SalesLastYear column is something new. As you can see, I include a subquery that summarizes the sales data for the North America territory. The aggregated value is then assigned to the Stephen Jiang row. The following table shows the results returned by the SELECT statement after the table has been updated:

FullName

TerritoryName

TerritoryGroup

SalesQuota

SalesYTD

SalesLastYear

Stephen Jiang

NULL

North America

NULL

0.00

16870456.7832

As the results show, all three columns have been updated, but only for the row specified in the WHERE clause. Now let’s look at what happens when you add a FROM clause to your UPDATE statement.

Using a FROM Clause to Retrieve Source Data

At times, you might want to retrieve values from a table other than the target table (the table you plan to update) when you modify data. In other words, you might want to update data in one table with data from another table.

To perform this type of modification, you can use a FROM clause to identify the source of that data. For example, the following UPDATE statement retrieves data from the vSalesPerson view to insert into the SalesQuota column of the SalesStaff table:

As you can see, I’ve added a FROM clause after the SET clause. The FROM clause joins the SalesStaff table to the vSalesPerson view, based on the salesperson’s full name. Because I’ve included the FROM clause, I can pull data from the vSalesPerson view to insert into the SalesStaff table. In this case, I retrieve data from the view’s SalesQuota column and insert it into the SalesQuota column of the SalesStaff table. The following table shows the SELECT statement results after I update the table:

FullName

TerritoryName

TerritoryGroup

SalesQuota

SalesYTD

SalesLastYear

Stephen Jiang

NULL

North America

NULL

0.00

16870456.7832

Michael Blythe

Northeast

North America

300000.00

0.00

1837926.8024

Linda Mitchell

Southwest

North America

250000.00

0.00

1511113.8306

Jillian Carson

Central

North America

250000.00

0.00

2097045.5139

Garrett Vargas

Canada

North America

250000.00

0.00

1701290.7414

Tsvi Reiter

Southeast

North America

300000.00

0.00

1942122.9889

Pamela Ansman-Wolfe

Northwest

North America

250000.00

0.00

2023412.1369

Shu Ito

Southwest

North America

250000.00

0.00

2177181.2999

José Saraiva

Canada

North America

250000.00

0.00

2140146.3876

David Campbell

Northwest

North America

250000.00

0.00

1440217.0816

Tete Mensa-Annan

Northwest

North America

300000.00

0.00

0.00

Syed Abbas

NULL

NULL

NULL

0.00

0.00

Lynn Tsoflias

Australia

Pacific

250000.00

0.00

2392476.4265

Amy Alberts

NULL

NULL

NULL

0.00

0.00

Rachel Valdez

Germany

Europe

250000.00

0.00

1373347.2813

Jae Pak

United Kingdom

Europe

250000.00

0.00

1717614.5665

Ranjit Varkey Chudukatil

France

Europe

250000.00

0.00

2516366.7481

As the results indicate, the SalesQuota values in the SalesStaff table now match the values in the vSalesPerson view.

You can also use a WHERE clause along with the FROM clause. In the following example, I update the SalesQuota column, but I do it only for rows whose TerritoryName column is not null:

Notice that I again join the SalesStaff table to the vSalesPerson view in the FROM clause. After the FROM clause, I add the WHERE clause, which specifies that TerritoryName values should not be null. Notice also that I add 50000 to the SalesQuota data that I pull from the view. After the update, the SELECT statement returns the following results:

FullName

TerritoryName

TerritoryGroup

SalesQuota

SalesYTD

SalesLastYear

Stephen Jiang

NULL

North America

NULL

0.00

16870456.7832

Michael Blythe

Northeast

North America

350000.00

0.00

1837926.8024

Linda Mitchell

Southwest

North America

300000.00

0.00

1511113.8306

Jillian Carson

Central

North America

300000.00

0.00

2097045.5139

Garrett Vargas

Canada

North America

300000.00

0.00

1701290.7414

Tsvi Reiter

Southeast

North America

350000.00

0.00

1942122.9889

Pamela Ansman-Wolfe

Northwest

North America

300000.00

0.00

2023412.1369

Shu Ito

Southwest

North America

300000.00

0.00

2177181.2999

José Saraiva

Canada

North America

300000.00

0.00

2140146.3876

David Campbell

Northwest

North America

300000.00

0.00

1440217.0816

Tete Mensa-Annan

Northwest

North America

350000.00

0.00

0.00

Syed Abbas

NULL

NULL

NULL

0.00

0.00

Lynn Tsoflias

Australia

Pacific

300000.00

0.00

2392476.4265

Amy Alberts

NULL

NULL

NULL

0.00

0.00

Rachel Valdez

Germany

Europe

300000.00

0.00

1373347.2813

Jae Pak

United Kingdom

Europe

300000.00

0.00

1717614.5665

Ranjit Varkey Chudukatil

France

Europe

300000.00

0.00

2516366.7481

You can also use a FROM clause that references a common table expression (CTE). You define the CTE prior to the UPDATE statement and then reference the CTE as you would a regular table or view. To demonstrate how this works, you first need to run the following UPDATE statements to modify two of the records in the SalesStaff table:

The reason for the update is to ensure that all three of the regional managers show the territory groups that they manage. After you run the updates, the three records should contain the following data:

FullName

TerritoryName

TerritoryGroup

SalesQuota

SalesYTD

SalesLastYear

Stephen Jiang

NULL

North America

NULL

0.00

16870456.7832

Syed Abbas

NULL

Pacific

NULL

0.00

0.00

Amy Alberts

NULL

Europe

NULL

0.00

0.00

Now we’re ready to demonstrate how to use a CTE with an UPDATE statement. In the following example, I create a CTE name cteSalesLastYear and then use the CTE to update the SalesStaff table:

In this code, I first use the CTE to aggregate the sales data for each territory group. Next, I define an UPDATE statement that includes a FROM clause. The clause joins the SalesStaff table to the CTE based on the TerritoryGroup value. In addition, I use a WHERE clause to limit the rows to those whose TerritoryName value is null. These are the regional manager rows.

In the SET clause, I retrieve the value for the SalesLastYear column from the TotalSales column in the CTE. Now the regional managers will each reflect the sales for his or her entire group, as shown in the following results:

FullName

TerritoryName

TerritoryGroup

SalesQuota

SalesYTD

SalesLastYear

Stephen Jiang

NULL

North America

NULL

0.00

16067101.6983

Syed Abbas

NULL

Pacific

NULL

0.00

2278548.9776

Amy Alberts

NULL

Europe

NULL

0.00

5340312.9485

As you can see, the FROM clause can be a useful tool for retrieving values from a table other than the target table. Now let’s look at how you update data stored in large value data types.

Updating Large Value Data

When updating columns configure with the varchar(max), nvarchar(max), or varbinary(max) data type, you should use the data type’s WRITE function to perform the modifications. The function takes three arguments: the new value to be inserted into the column, the starting point for inserting that value, and the number of characters to replace with the new value.

To demonstrate how this works, let’s first create a table that includes a nvarchar(max) column:

As you can see, I create a table named Products. The table includes the ProdDescrip column, which I’ve configured with the nvarchar(max) data type. I populate the table with five rows from the vProductAndDescription view. After I insert the data, the SELECT statement returns the following results:

ProdID

ProdName

ProdDescrip

864

Classic Vest, S

Light-weight, wind-resistant, packs to fit into a pocket.

865

Classic Vest, M

Light-weight, wind-resistant, packs to fit into a pocket.

866

Classic Vest, L

Light-weight, wind-resistant, packs to fit into a pocket.

712

AWC Logo Cap

Traditional style with a flip-up brim; one-size fits all.

861

Full-Finger Gloves, S

Synthetic palm, flexible knuckles, breathable mesh upper. Worn by the AWC team riders.

Once we’ve created the table, we’re ready to update the ProdDescrip column. In the following example, I use the WRITE function to modify the product description for product number 861:

First, I specify the column to be updated (ProdDescrip), followed by a period, and then followed by the WRITE function. The function’s three arguments are enclosed in parentheses, separated by commas. The first argument-AdventureWorks-is the new text that will be inserted in the column. The second argument-70-is the starting point for that new text. (Note that the starting point is based on a 0-based ordinal position.) In this example, the starting point is the A in the AWC in the description. The final argument-3-indicates that three characters should be replaced by the new text. As a result, the AWC will be replaced by AdventureWorks.

When you run the SELECT statement after the update, the description for product 861 should now look like the results shown in the following table:

ProdID

ProdName

ProdDescrip

861

Full-Finger Gloves, S

Synthetic palm, flexible knuckles, breathable mesh upper. Worn by the AdventureWorks team riders.

NOTE: If you try this example against the AdventureWorks database, rather than AdventureWorks2008, the primary key values might be different, in which case, the product ID 861 will not work. You’ll have to modify the CREATE TABLE or UPDATE statement as necessary to accommodate the different data.

As you’ve seen in this article, the UPDATE statement provides a great deal of flexibility for modifying data in a SQL Server database. However, the examples I’ve explained here provide only the basics of how to create such statements. The statement itself is far more extensive than what I’ve demonstrated in the examples. For this reason, be sure to see the topics “Changing Data by Using Update” and “UPDATE (Transact-SQL)” in SQL Server Books Online. There you’ll find plenty of additional information and a variety of examples.

About the author

Robert Sheldon

See Profile

Robert is a freelance technology writer based in the Pacific Northwest. He’s worked as a technical consultant and has written hundreds of articles about technology for both print and online publications, with topics ranging from predictive analytics to 5D storage to the dark web. He’s also contributed to over a dozen books on technology, developed courseware for Microsoft’s training program, and served as a developmental editor on Microsoft certification exams. When not writing about technology, he’s working on a novel or venturing out into the spectacular Northwest woods.

Robert's contributions