UPDATETEXT (Transact-SQL)UPDATETEXT (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

更新现有 text、ntext 或 image 字段。Updates an existing text, ntext, or image field. 使用 UPDATETEXT 可以只更改 text、ntext 或 image 列的一部分。Use UPDATETEXT to change only a part of a text, ntext, or image column in place. 使用 WRITETEXT 可以更新和替换整个 text、ntext 或 image 字段。Use WRITETEXT to update and replace a whole text, ntext, or image field.

重要

此功能处于维护模式并且可能会在 Microsoft SQL Server 将来的版本中被删除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.请改用大值数据类型和 UPDATE 语句的 .WRITE 子句。Use the large-value data types and the . WRITE clause of the UPDATE statement instead.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
UPDATETEXT [BULK] { table_name.dest_column_name dest_text_ptr }  
  { NULL | insert_offset }  
     { NULL | delete_length }  
     [ WITH LOG ]  
     [ inserted_data  
    | { table_name.src_column_name src_text_ptr } ]  

参数Arguments

BULKBULK
启用上载工具来上载二进制数据流。Enables upload tools to upload a binary data stream. 该数据流必须由该工具在 TDS 协议级别提供。The stream must be provided by the tool at the TDS protocol level. 在数据流不存在时,查询处理器将忽略 BULK 选项。When the data stream is not present the query processor ignores the BULK option.

重要

我们建议不要在基于 SQL ServerSQL Server 的应用程序中使用 BULK 选项。We recommend that the BULK option not be used in SQL ServerSQL Server-based applications. SQL ServerSQL Server 的未来版本中可能会更改或删除该选项。This option might be changed or removed in a future version of SQL ServerSQL Server.

table_name .table_name . dest_column_namedest_column_name
要更新的表以及 text、ntext 或 image 列的名称。Is the name of the table and text, ntext, or image column to be updated. 表名和列名必须符合标识符规则。Table names and column names must comply with the rules for identifiers. 可以选择是否指定数据库名和所有者名。Specifying the database name and owner names is optional.

dest_text_ptrdest_text_ptr
指向要更新的 text、ntext 或 image 数据的文本指针的值(由 TEXTPTR 函数返回)。Is a text pointer value (returned by the TEXTPTR function) that points to the text, ntext, or image data to be updated. dest_text_ptr 必须是二进制 (16)。dest_text_ptr must be binary( 16 ).

insert_offsetinsert_offset
以零为基的更新起始位置。Is the zero-based starting position for the update. 对于 text 或 image 列,insert_offset 是在插入新数据前要从现有列的起点跳过的字节数。For text or image columns, insert_offset is the number of bytes to skip from the start of the existing column before inserting new data. 对于 ntext 列,insert_offset 是字符数(每个 ntext 字符占用 2 个字节)。For ntext columns, insert_offsetis the number of characters (each ntext character uses 2 bytes). 从此基数为零的起始点开始的现有 text、ntext 或 image 数据向右移,为新数据留出空间。The existing text, ntext, or image data starting at this zero-based starting position is shifted to the right to make room for the new data. 值为 0 表示将新数据插入现有数据的开始处。A value of 0 inserts the new data at the beginning of the existing data. 值为 NULL 则将新数据追加到现有数据值后。A value of NULL appends the new data to the existing data value.

delete_lengthdelete_length
从 insert_offset 位置开始的、要从现有 text、ntext 或 image 列中删除的数据长度。Is the length of data to delete from the existing text, ntext, or image column, starting at the insert_offset position. delete_length 值为 text 和 image 列指定时以字节为单位,为 ntext 列指定时以字符为单位。The delete_lengthvalue is specified in bytes for text and image columns and in characters for ntext columns. 每个 ntext 字符占用 2 个字节。Each ntext character uses 2 bytes. 值为 0 表示不删除数据。A value of 0 deletes no data. 值为 NULL 则删除现有 text 或 image 列中从 insert_offset 位置开始到末尾的所有数据。A value of NULL deletes all data from the insert_offset position to the end of the existing text or image column.

WITH LOGWITH LOG
日志记录由数据库的当前恢复模式决定。Logging is determined by the recovery model in effect for the database.

inserted_datainserted_data
要插入到 insert_offset 位置现有 text、ntext 或 image 列中的数据。Is the data to be inserted into the existing text, ntext, or image column at the insert_offset location. 这是单个 char、nchar、varchar、nvarchar、binary、varbinary、text、ntext 或 image 值。This is a single char, nchar, varchar, nvarchar, binary, varbinary, text, ntext, or image value. inserted_data 可以是文字或变量。inserted_data can be a literal or a variable.

table_name.src_column_nametable_name.src_column_name
用作插入数据源的表和 text、ntext 或 image 列的名称。Is the name of the table and text, ntext, or image column used as the source of the inserted data. 表名和列名必须符合标识符规则。Table names and column names must comply with the rules for identifiers.

src_text_ptrsrc_text_ptr
指向用作插入数据源的 text、ntext 或 image 列的文本指针值(由 TEXTPTR 函数返回)。Is a text pointer value (returned by the TEXTPTR function) that points to a text, ntext, or image column used as the source of the inserted data.

备注

scr_text_ptr 值不能与 dest_text_ptr 值相同。scr_text_ptr value must not be the same as dest_text_ptrvalue.

RemarksRemarks

新插入的数据可以是单个 inserted_data 常量、表名、列名或文本指针。Newly inserted data can be a single inserted_data constant, table name, column name, or text pointer.

Update 操作Update action UPDATETEXT 参数UPDATETEXT parameters
替换现有数据To replace existing data 指定一个非空 insert_offset 值、非零 delete_length 值和要插入的新数据。Specify a nonnull insert_offset value, a nonzero delete_length value, and the new data to be inserted.
删除现有数据To delete existing data 指定非空 insert_offset 值和非零 delete_length。Specify a nonnull insert_offset value and a nonzero delete_length. 不指定要插入的新数据。Do not specify new data to be inserted.
插入新数据To insert new data 指定 insert_offset 值、为 0 的 delete_length 和要插入的新数据。Specify the insert_offset value, a delete_length of 0, and the new data to be inserted.

为获得最佳性能,建议按照块区大小为 8,040 字节倍数的方式插入或更新数据。For best performance we recommend that text, ntext and image data be inserted or updated in chunks sizes that are multiples of 8,040 bytes.

SQL ServerSQL Server 中,可能存在指向 text、ntext 或 image 数据的行内文本指针,但可能无效。In SQL ServerSQL Server, in-row text pointers to text, ntext, or image data may exist but may not be valid. 有关 text in row 选项的信息,请参阅 sp_tableoption (Transact-SQL)For information about the text in row option, see sp_tableoption (Transact-SQL). 有关使文本指针无效的信息,请参阅 sp_invalidate_textptr (Transact-SQL)For information about invalidating text pointers, see sp_invalidate_textptr (Transact-SQL).

若要将 text 列初始化为 NULL,请使用 WRITETEXT;UPDATETEXT 将 text 列初始化为空字符串。To initialize text columns to NULL, use WRITETEXT; UPDATETEXT initializes text columns to an empty string.

PermissionsPermissions

需要对指定表的 UPDATE 权限。Requires UPDATE permission on the specified table.

示例Examples

以下示例将文本指针放入局部变量 @ptrval 中,然后使用 UPDATETEXT 更新拼写错误。The following example puts the text pointer into the local variable @ptrval, and then uses UPDATETEXT to update a spelling error.

备注

若要运行此示例,必须安装 pubs 数据库。To run this example, you must install the pubs database.

USE pubs;  
GO  
ALTER DATABASE pubs SET RECOVERY SIMPLE;  
GO  
DECLARE @ptrval binary(16);  
SELECT @ptrval = TEXTPTR(pr_info)   
   FROM pub_info pr, publishers p  
      WHERE p.pub_id = pr.pub_id   
      AND p.pub_name = 'New Moon Books'  
UPDATETEXT pub_info.pr_info @ptrval 88 1 'b';  
GO  
ALTER DATABASE pubs SET RECOVERY FULL;  
GO  

另请参阅See Also

READTEXT (Transact-SQL) READTEXT (Transact-SQL)
TEXTPTR (Transact-SQL) TEXTPTR (Transact-SQL)
WRITETEXT (Transact-SQL)WRITETEXT (Transact-SQL)