Unless you are working on a reporting-only application you will probably need to update tables in your SQL Server database. To update rows in a table you use the UPDATE statement. There are a number of different ways you can use the UPDATE statement. In this level I will be discussing how to find and update records in your database, and discuss the pitfalls you might run into when using the UPDATE statement.

Basic Syntax of UPDATE Statement

There are a number of different formats that the UPDATE statement can use. I will be showing you the UPDATE statement syntax that is most commonly used. Below is the basic syntax for the UPDATE statement:

UPDATE <object_name> 
    SET <column_name> =  [ ,…n]
    [ <OUTPUT Clause> ]
    [ FROM <table_source> [ ,…n]
    [ WHERE  <search_condition> 

Where:

<object_name> - is the table that will be updated.

<column_name> - is a column that will be updated

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

<table_source> - is a table, view or derived table that provide the <value> to be used to update a <column_name>

<search_condition> - defines the criteria a row must meet in order to be updated.

For the complete syntax of the UPDATE statement refer to Books Online.

In order to demonstrate the use of the UPDATE statement I will need a couple of different tables. The code in Listing 1 can be used to create the tables I will be using in my examples.

SET NOCOUNT ON;
USE tempdb;
go
CREATE TABLE Product (
ID int identity not null, 
ProductName varchar(25) not null, 
Price decimal(6,2) not null
);
INSERT INTO Product 
VALUES ('Widget',25.99),
('WingDng',18.87),
       ('DingDong',1.99),
       ('DoDad',87.34);
CREATE TABLE New_Prices (      
ID int not null, 
ProductName varchar(25) not null, 
Price decimal(6,2) not null
);
INSERT INTO New_Prices 
VALUES (1,'Widget',26.99),
       (2,'WingDing',19.31),
       (3,'DingDong',2.99),
        (4,'Doo-Dads',97.21);

Listing 1: Code to create tables used in examples

Updating a Single Column in a Single Row

You may find you need to update data in a table for a number of different reasons. When I created my Product table I incorrectly typed the names for some of my products. One of my typos is where I typed “WIngDng” when it should have been “WingDing”. To update this single column on a single row I can run the UPDATE statement in Listing 2.

UPDATE Product 
  SET ProductName = 'WingDing'
WHERE ProductName = 'WingDng';

Listing 2: Update a single column

In Listing 2 I used the <search_condition> of the UPDATE statement syntax to identify the criteria that a row should meet in order for it to be updated. In this case, I need to search for the value “WingDng” in the ProductName columnBy checking if the value in the ProductName column was equal to my misspelled string I was able to identify the single row that needed to be updated. The SET clause of the UPDATE statement was used to specify the actual column value that should be used in ProductName column, namely “WingDing”.

Pitfall of using the UPDATE Statement

Care needs to be taken when you are writing UPDATE statements to make sure you don’t incorrectly update more or fewer rows then you want. One of the common problems you might run across is forgetting to provide a <search_condition>, or having a <search_condition> that identifies too many or too few rows than the number you want to update. In Listing 3 I have a <search_condition> that causes my update statement to update more rows than I want.

SET NOCOUNT OFF;
UPDATE Product 
   SET Price = 19.27
WHERE ProductName like 'W%'; 

Listing 3: Updating too many records

If you run the code in Listing 3 you will find it updates two rows because of my <search criteria>, when my original intent was to only update the single “WingDing” row.

If you are writing UPDATE statements to resolve some data integrity problem with data in a table and you are not exactly sure which rows will be updated using a particular <search_condition> you should first write a SELECT statement using the <search_condition> you think is correct. Then once your SELECT statement returns the appropriate rows to be updated you can then turn your SELECT statement into an UPDATE statement. This practice provides you a method to help make sure only the correct rows are updated.

Changing Multiple Columns Using a Single UPDATE Statement

A single UPDATE statement is able to update more than a single column. This can be done by referencing multiple column names in the SET clause, as shown in Listing 4.

UPDATE Product
   SET ProductName = 'Do-Dads',
       Price = 81.58
WHERE ProductName = 'DoDad';

Listing 4: Updating two columns with a single UPDATE statement

In Listing 4 I updated both the ProductName and Price column with a single UPDATE statement. In this example those columns will only be updated in the rows that had a ProductName of “DoDad”.

Updating Columns Based on Column Values from another Table

You don’t always have to use a literal values to update columns when using the UPDATE statement. You can update columns in one table using the values found in another table. In Listing 5 I show how to use the FROM clause to use column values from one table to update another.

UPDATE Product  
   SET ProductName = N.ProductName, 
       Price = N.Price
FROM Product P JOIN New_Prices N
     ON P.ID = N.ID;

Listing 5: Updating columns based on records from another table

The code in Listing 5 uses the FROM clause to match rows between the Product and New_Prices tables based on the ID column. For every matching row I take the ProductName and Price column from the New_Prices table to update the corresponding columns in the Product table.

You do not have to update every matching row when you use the FROM clause. If you add a WHERE condition like I have done in Listing 6 you can identify which matching rows are to be updated.

UPDATE Product  
   SET Price = N.Price
FROM Product P JOIN New_Prices N
     ON P.ID = N.ID
WHERE P.ProductName = 'Doo-Dads';

Listing 6: Constraining which columns to update based on a WHERE condition.

In Listing 6 I only updated the Price column on those rows that had a ProductName of “Doo-Dads”.

Using the .WRITE Clause with an UPDATE statement

If you have columns that are defined using large data types in your table, like varchar(max), ncharchar(max) or varbinary(max) then you can use the .WRITE clause to update these columns. The .WRITE clause was introduced with SQL Server 2005. This clause can be used to update a substring or to append data to end of a large data type column.

The .WRITE clause has the following syntax:

.WRITE ( expression, @Offset , @Length )

Where:

expression - is the character string you want to insert in the large character data type column

@Offset – is the starting potion where the expression will be written

@Length – is the number of characters that will be replaced by the expression

To show how to use the .WRITE clause let me go through a couple of examples. For my first example which can be found in Listing 7, I will be updating some text near the end of a column that contains a very large character string. For this example, I first start off by creating a new table that contains the large data type column that I want to update.

  SET NOCOUNT ON;
  USE tempdb;
  GO
  CREATE TABLE MyDemo (LargeColumn varchar(max));
  INSERT INTO MyDemo (LargeColumn)
     VALUES (REPLICATE(CAST('A' AS varchar(max)),60000)
                     + 'etadpu ot erehw si ereH');
  SELECT REVERSE(LargeColumn) 'Reverse of LargeColumn'
     FROM MyDemo;
  UPDATE MyDemo
    SET LargeColumn.WRITE('nmuloc ym detadpu evah I',60000,18);
  SELECT REVERSE(LargeColumn) 'Reverse of LargeColumn' FROM MyDemo;

Listing 7: Updating the middle of a large character string

When I run the code in listing 7 I get the output in Report 1.

Reverse of LargeColumn ----------------------------------------------------------------------------- Here is where to updateAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA... Reverse of LargeColumn ----------------------------------------------------------------------------- Here I have updated my columnAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA...

Report 1: Output from running Listing 7

If you review the code in Listing 7 you will see I create a table MyDemo and insert a very large character string, of over 60,000 characters in length, into column LargeColumn. Once the record is inserted, I display the LargeColumn value in the reverse order so you can see the character values at the end of the string. I then use the .WRITE clause to update 18 characters starting at offset 60,000 with the value of “nmuloc ym detadpu evah I”. I then use another SELECT statement that uses the REVERSE function again to display the end of the text value for the updated column.

The .WRITE clause is also useful for appending information to a large data column. In Listing 8, I use the .WRITE clause to add a character string to the end of my large text column named LargeColumn.

UPDATE MyDemo
  SET LargeColumn.WRITE(' .txet siht dedneppa won evah I'
           ,LEN(LargeColumn),0);
SELECT REVERSE(LargeColumn) 'Reverse of LargeColumn' 
    FROM MyDemo;

Listing 8: Appending to the end of a large string

When I run the code in Listing 8 I get the output in Report 2.

Reverse of LargeColumn

-----------------------------------------------------------------------------

I have now appended this text. Here I have updated my columnAAAAAAAAAAAAA...

Report 2: Output from running Listing 8

The code in Listing 8 uses the LEN function to identity the offset to start appending characters to my LargeColumn value, and then uses a length value of zero to identify that no characters are going to be replaced. In reality any number could have been used to represent the length parameter of the .WRITE clause because I am adding characters at the end of the existing column value.

Note that you can’t use the .WRITE clause to update a large column when the column contains a null value. You must first update the null value to a non-null value, and then you can use the .WRITE clause to update the large data type column.

Using the OUTPUT Clause with an UPDATE statement

When using the UPDATEstatement SQL Server populates two different pseudo tables named INSERTED and DELETED. The INSERTED pseudo-table will contain all of the columns values for the entire updated rows after the UPDATE statement has completed. Whereas the DELETED pseudo-table contains the old values all of the column values for the entire update row prior to the update statement being performed. You can think of the DELETED pseudo-table as containing the before images of updated rows, whereas the INSERTED pseudo-table contains the after images of updated rows. By using these two tables you can get the before and after images of rows that were updated. If you use the OUTPUT clause on an UPDATE statement you can expose the values from these pseudo-tables to your application. To see how to use these pseudo-tables to return data to your calling application, review the code in Listing 9.

UPDATE Product
   SET Price = 2.11
   OUTPUT DELETED.*,INSERTED.*
WHERE ProductName = 'DingDong';

Listing 9: Using the OUTPUT clause on an UPDATE statement

When I run the code in Listing 9 from within a query window, the output in Report 3 is returned.

ID ProductName Price ID ProductName Price

--- ------------- ---------- --- ------------- ----------

3 DingDong 2.99 3 DingDong 2.11

Report 3: Output returned to application when running code in listing 9

If you review the code in Listing 9 you will see that the OUTPUT clause contains references to both the DELETED and INSERTED pseudo-tables. By specifying the <dot>* notation following each of those pseudo.tables I told SQL Server to return the before and after images for all column values for the updated rows. By referencing both pseudo-tables, the first set of column values returned in my example represent the before image, whereas the second set of column values returned represent the values of my updated columns after the UPDATE statement was performed.

The values contained in these two pseudo-tables can also be placed in a table or table variable. Additionally you don’t have to output each of the pseudo-table column values either. This can be demonstrated by looking at the code in Listing 10.

DECLARE @ProductPriceAudit TABLE (
   ID INT, 
   BeforePrice decimal(6,2),
   AfterPrice decimal(6,2));
UPDATE Product
   SET Price = 27.98
   OUTPUT DELETED.ID,DELETED.Price,INSERTED.Price 
      INTO @ProductPriceAudit
WHERE ID = 1;
SELECT * FROM @ProductPriceAudit;

Listing 10: Using the OUTPUT clause on an UPDATE statement to capture pseudo table column values in a temporary table variable

The code in listing 10 captured the before and after values of the Price column for the ID’s that were updated.

For a complete explanation of how to use the OUTPUT clause and it limitations refer the Books Online topic on “OUTPUT clause”

Maintaining Data with the UPDATE Statements

It is good to know how to use the FROM and WHERE clause to identify which rows to update, and to specify the column values to use in an UPDATE statement. Being able use the INSERTED and DELETED pseudo-tables comes in handy when you need to provide an audit trail for your update processes. Having a good understanding of how to build an UPDATE statement allows you to maintain the data in your database.