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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
USE AdventureWorks2008 GO IF OBJECT_ID ('SalesStaff', 'U') IS NOT NULL DROP TABLE dbo.SalesStaff GO SELECT FirstName + ' ' + LastName AS FullName, TerritoryName, TerritoryGroup, SalesQuota, SalesYTD, SalesLastYear INTO SalesStaff FROM Sales.vSalesPerson GO SELECT * FROM SalesStaff |
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.
1 2 3 4 5 |
UPDATE SalesStaff SET SalesQuota = 250000 GO SELECT * FROM SalesStaff |
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:
1 |
<column> = <value> |
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:
1 2 3 4 5 |
UPDATE SalesStaff SET SalesQuota = SalesQuota + 50000 GO SELECT * FROM SalesStaff |
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:
1 2 3 4 5 6 7 8 |
UPDATE SalesStaff SET SalesQuota = SalesQuota + 50000, SalesYTD = 0, SalesLastYear = SalesLastYear * 1.05 GO SELECT * FROM SalesStaff |
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:
1 2 3 4 5 6 7 |
UPDATE SalesStaff SET TerritoryName = 'UK' WHERE TerritoryName = 'United Kingdom' GO SELECT * FROM SalesStaff WHERE TerritoryName = 'UK' |
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:
1 2 3 4 5 6 7 |
UPDATE SalesStaff SET TerritoryName = 'United Kingdom' WHERE FullName = 'Jae Pak' GO SELECT * FROM SalesStaff WHERE FullName = '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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
UPDATE SalesStaff SET TerritoryGroup = 'North America', SalesQuota = NULL, SalesLastYear = ( SELECT SUM(SalesLastYear) FROM SalesStaff WHERE TerritoryGroup = 'North America' AND TerritoryName IS NOT NULL GROUP BY TerritoryGroup ) WHERE FullName = 'Stephen Jiang' GO SELECT * FROM SalesStaff WHERE FullName = '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:
1 2 3 4 5 6 7 8 9 |
UPDATE SalesStaff SET SalesQuota = sp.SalesQuota FROM SalesStaff ss INNER JOIN Sales.vSalesPerson sp ON ss.FullName = (sp.FirstName + ' ' + sp.LastName) SELECT * FROM SalesStaff |
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:
1 2 3 4 5 6 7 8 9 10 |
UPDATE SalesStaff SET SalesQuota = sp.SalesQuota + 50000 FROM SalesStaff ss INNER JOIN Sales.vSalesPerson sp ON ss.FullName = (sp.FirstName + ' ' + sp.LastName) WHERE ss.TerritoryName IS NOT NULL SELECT * FROM SalesStaff |
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:
1 2 3 4 5 6 7 8 9 10 11 12 |
UPDATE SalesStaff SET TerritoryGroup = 'Pacific' WHERE FullName = 'Syed Abbas' GO UPDATE SalesStaff SET TerritoryGroup = 'Europe' WHERE FullName = 'Amy Alberts' GO SELECT * FROM SalesStaff WHERE TerritoryName IS NULL |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
WITH cteSalesLastYear (TerritoryGroup, TotalSales) AS ( SELECT TerritoryGroup, SUM(SalesLastYear) FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL GROUP BY TerritoryGroup ) UPDATE SalesStaff SET SalesLastYear = sly.TotalSales FROM SalesStaff AS ss INNER JOIN cteSalesLastYear AS sly ON ss.TerritoryGroup = sly.TerritoryGroup WHERE ss.TerritoryName IS NULL GO SELECT * FROM SalesStaff WHERE TerritoryName IS NULL |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
USE AdventureWorks2008 GO IF OBJECT_ID ('Products', 'U') IS NOT NULL DROP TABLE dbo.Products GO CREATE TABLE Products ( ProdID INT NOT NULL, ProdName NVARCHAR(50) NOT NULL, ProdDescrip NVARCHAR(MAX) NOT NULL ) GO INSERT INTO Products SELECT TOP 5 ProductID, Name, Description FROM Production.vProductAndDescription WHERE CultureID = 'en' GO SELECT * FROM Products |
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:
1 2 3 4 5 6 7 |
UPDATE Products SET ProdDescrip.WRITE('AdventureWorks', 70, 3) WHERE ProdID = 861 GO SELECT * from Products WHERE ProdID = 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.
Load comments