SQL Server's UPDATE statement is apparently simple, but complications such as the FROM clause can cause puzzlement. Bob Sheldon starts simply, and introduces the more complex forms painlessly.  (The OUPUT clause will be tackled separately, along with the use of variables in UPDATE)

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:

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.

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.