Understanding the Column.WRITE Expression
SQL Server 2008 provides a capability to append or insert text into existing text columns as part of the UPDATE statement. This is achieved using the column. WRITE expression. Let’s examine the column.WRITE expression in more detail. The elements of the column.WRITE statement are shown in Example 5.
Example 5. UPDATE...SET Column.WRITE(...) Statement—Core Elements
UPDATE table_or_view
column_to_update { .WRITE (new_value, @Offset, @Length) }
[FROM ...
WHERE ... ]
|
The column.WRITE statement tells SQL Server to append, remove, or insert text into the existing text value in column_name (see Example 6). The @Length characters starting from @Offset of column_name are replaced with the new_value. If the new_value is set to NULL, @Length is ignored, and the value in column_name is truncated at the specified @Offset. Only columns of varchar(max), nvarchar(max), or varbinary(max) can be specified with this clause. When using the .WRITE method, you cannot qualify the column_name with a table name or table alias.
@Offset is the number of characters after which the new_value replaces the existing characters. If @Offset is NULL, the new_value will be appended to the end of the column, regardless of the @Length parameter. If @Offset is larger than the length of the column value, the operation will fail, and an error will be reported.
@Length is the number of characters to be replaced by the new_value. If @Length is NULL, the update operation removes all data from @Offset to the end of the column_name value.
Example 6. Using the Column.WRITE Statement
USE AdventureWorks
GO
-- Create a new table to hold advertising copy
CREATE TABLE AdvertisingCopy (AdvertisementName nvarchar(100),
AdvertisementText varchar(max))
GO
INSERT AdvertisingCopy VALUES
('Reflector', 'Be seen at night.')
GO
SELECT * FROM AdvertisingCopy
GO
-- AdvertisementText is 'Be seen at night.'
UPDATE AdvertisingCopy
SET AdvertisementText.WRITE(' by traffic coming behind you.', 16, 30)
WHERE AdvertisementName = 'Reflector'
SELECT * FROM AdvertisingCopy
GO
-- AdvertisementText is 'Be seen at night by traffic coming behind you.'
UPDATE AdvertisingCopy
SET AdvertisementText.WRITE('The most advanced reflector ever. ', 0, 0)
WHERE AdvertisementName = 'Reflector'
SELECT * FROM AdvertisingCopy
GO
-- AdvertisementText is 'The most advanced reflector ever. Be seen at night
by traffic coming behind you.'
UPDATE AdvertisingCopy
SET AdvertisementText.WRITE(Null, 34, NULL)
WHERE AdvertisementName = 'Reflector'
SELECT * FROM AdvertisingCopy
GO
-- AdvertisementText is 'The most advanced reflector ever.'
UPDATE AdvertisingCopy
SET AdvertisementText.WRITE('(Well, sort of...).', 28, NULL)
WHERE AdvertisementName = 'Reflector'
SELECT * FROM AdvertisingCopy
GO
-- AdvertisementText is 'The most advanced reflector (Well, sort of...).'
|
Warning
Remember that only columns of varchar(max), nvarchar(max), or varbinary(max) can be specified with this clause. When using the .WRITE method, you cannot qualify the column_name with a table name or table alias.
Exercise . Using the UPDATE Statement
|
In this exercise, we will practice using the UPDATE statement.
Before you begin, you must have the following software installed on your computer:
This exercise does not
depend on you completing any previous exercises. We will be modifying
rows in the Production.Product table in the AdventureWorks database.
Open SQL Server Management Studio. Click Start | All Programs | Microsoft SQL Server 2008 | SQL Server Management Studio. Create a new query against the AdventureWorks database. Examine and execute the following SELECT statement. This statement shows all rows with Road-350-W as the product model. SELECT Product.Name, DiscontinuedDate, ProductModel.Name as Model
FROM Production.Product JOIN Production.ProductModel
ON Product.ProductModelID = ProductModel.ProductModelID
WHERE ProductModel.Name = 'Road-350-W'
ORDER BY DiscontinuedDate DESC
GO Create and execute an UPDATE statement that will set DiscontinuedDate to today’s date for all products with Road-350-W as the product model. Use the UPDATE statement to discontinue all products of model Road-350-W: UPDATE Production.Product
SET DiscontinuedDate = GETDATE()
FROM Production.Product JOIN Production.ProductModel
ON Product.ProductModelID = ProductModel.ProductModelID
WHERE ProductModel.Name = 'Road-350-W'
GO Execute the SELECT statement again. Notice that the DiscontinuedDate has changed from NULL to today’s date value.
|
Tip
To quickly memorize the syntax of the UPDATE statement, think “UPDATE table SET column = value FROM table WHERE condition.” The verbs are UPDATE...SET...FROM...WHERE.
