Chapter 12: Updating Data from Microsoft SQL Server 2005 | Solid Quality Learning, Microsoft Corporation Staff - Microsoft SQL Server 2005 Database Essentials Step by Step

Download CD Content

After completing this chapter, you will be able to:

Update information in your databases
Manage update issues by using triggers
Use ADO.NET components to perform update operations

Using the UPDATE Statement

You use the UPDATE statement to modify information in your data tables. The basic syntax for updates is the following:

 UPDATE [<ServerName>].[<Database>].[<Schema>].[<TableName>]    SET [<ColumnName>] = <NewValue>>       ,....  WHERE <Search Conditions,,>

As usual, if you execute the UPDATE sentence with a connection to the current database, you can simply use the <SchemaName>.<TableName> form without including the server and database names .

You can use the UPDATE sentence without a WHERE condition. A developer uses this option to modify the entire table with some calculation, such as incrementing or decrementing the products prices, or with any other massive change. If you want to update specific information, you must include the WHERE condition in your sentence. Moreover, the WHERE condition must be specific enough to update just one row.

Creating an UPDATE Sentence with SQL Server Management Studio

To create an UPDATE sentence using SQL Server Management Studio, perform the following steps.

Creating an UPDATE Sentence

Right-click the desired table.
Choose Script Table As.
Choose Update To.
Choose New Query Editor Window. SQL Server Management Studio will create a template for you.

Important

If you work through the procedures in this chapter using the AdventureWorks database, it is important to back up your database first.

Defining the WHERE Condition

Similar to the DELETE statement, you can use any or all columns of a table to create the WHERE condition. You can enhance the performance of the UPDATE statement by using the following guidelines:

Use the Primary.Key column/s in the condition.
Use columns that are part of an index.

Assigning New Values to Columns

The values assigned to columns must match the datatype. You can assign various types of values to a column.

NULL Can be used when the column accepts NULLs
DEFAULT Can be used when the column has a default value defined
SQL Functions You must use a function that returns the same datatype accepted by the column. Some examples include:
- GetDate() Used to assign the actual server-side date and time values
- NEWID() Used to assign a new Id to a unique identifier column
- user_name() Used to assign the current username, which is useful for auditing purposes

Your own values You can supply any valid value.

During any column update, you must match the datatype and stay within the max values that the column accepts. In the following sentence, the ModifiedDate receives a valid date.

 UPDATE [AdventureWorks].[Sales].[SalesReason]    SET [ModifiedDate] = 01/01/2006  WHERE SalesReasonID=1

However, if you look at the row content, you notice that the column in that row contains 01/01/1900 12:00:00 a.m. Microsoft SQL Server 2005 assumes that the date you enter is an arithmetic operation: 1 divided by 1 divided by 2006. The query analyzer executes the operation and converts the result into a new date, which results in day 0 since the calculated value is less than 1.

You cannot use Visual Basic syntax, such as the following:

 UPDATE [AdventureWorks].[Sales].[SalesReason]    SET [ModifiedDate] = #01/01/2006#  WHERE SalesReasonID=1

The above code generates the following syntax error message:

 Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '#'.

You must enter the date as a string and cast it to a datetime value.

 UPDATE [AdventureWorks].[Sales].[SalesReason]    SET [ModifiedDate] = cast('01/01/2006'as datetime)  WHERE SalesReasonID=1

However, the issue has futher complications. The following sentence changes the modified date to Oct 31, 2006.

 UPDATE [AdventureWorks].[Sales].[SalesReason]    SET [ModifiedDate] = cast('10/31/2006'as datetime) WHERE SalesReasonID=1

The code above is valid if you are from the United States or any country that uses the mm/dd/yyyy date format. In other countries, the format might be different. For example, it might be dd/mm/yyyy, as in all Spanish-speaking countries , or yyyy.mm.dd, as in Japan.

The SQL Server engine assumes the date to be formatted according to its own language. If you have installed the English version of SQL Server 2005, it will interpret dates in the US or UK format. Nowadays, increasing numbers of applications are Web enabled and have clients from around the world. Each user will enter a date in a Web page using his customary format. Thus, if you use input from Japan, you will receive many conversion errors.

To avoid this problem, you can use the CONVERT function instead of the CAST function. CONVERT accepts another argument or style to define the format to use in the datatype conversion.

Tip	Search for the CAST and CONVERT functions in SQL Server Books Online to review the complete list of style values.

Using Arithmetic to Update Information

You can update a group of rows or the entire content of a table by adjusting the WHERE condition. This is typically done when you need to update the same column by applying a differential value such as incrementing or decrementing a discount, adding or subtracting amounts, and so on.

You do not need to use a variable to perform the calculation. You can use the calculus directly as the right part of the assignment, as follows :

 UPDATE [AdventureWorks].[Production].[Product]    SET [ListPrice] = [ListPrice] * 0.9  WHERE ProductSubcategoryID=1

Changing the Content of a Large (MAX) Column

If you need to modify part of the content of a large field, such as a column with a varchar(MAX), nvarchar(MAX), or nvarbinary(MAX) datatype, you can simply change that part instead of modifying the entire content. You can apply the following modifier to the UPDATE sentence:

 UPDATE <table_Name>    SET <Column_Name>.WRITE(<New_Value>,<Offset>,<Length>)

Note	The offset value starts with zero.

Tip	To enhance performance, try to use .WRITE with 8040-byte chunks .

Changing the Content of a Large (BLOB) Column

Changing the content of a text or ntext column may need a different approach. If you want to change the entire content, you need to use the WRITETEXT sentence, which was the only way to accomplish this until the arrival of SQL Server 2005. If you want to partially change the content, you can use UPDATETEXT. Using these sentences requires that you obtain a pointer to the column before executing them. You will use the pointer to implement the UPDATETEXT or WRITETEXT sentences. For more information, search for UPDATETEXT in SQL Server Books Online.

Important

Starting with SQL Server 2005, avoid using the WRITETEXT or UPDATETEXT sentences, for they are not guaranteed to exist in future versions of SQL Server. Using the MAX modifier for any long blocks of content allows you to enhance the database. You should always use the .WRITE modifier.