UPDATETEXT question

[Artie]

I have a question on the UPDATETEXT function (SQL 2000)
The below query works and only updates the record where p.pub_id =
pr.pub_id.
I just don't quite understand why only 1 record is updated when the
UPDATETEXT statement
does not specify anything except pointer value. Is it no possible for 2
rows in a table with a text column to have the same pointer value?
Does this query scan all pr_info in the pub_info table?

USE pubs
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
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
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
GO
Also, what is the importance of 'select into/bulkcopy', 'true' ?

Thanks

[Erland Sommarskog]

Artie (ar*******@yahoo.com) writes:

I have a question on the UPDATETEXT function (SQL 2000)
The below query works and only updates the record where p.pub_id =
pr.pub_id.
I just don't quite understand why only 1 record is updated when the
UPDATETEXT statement
does not specify anything except pointer value. Is it no possible for 2
rows in a table with a text column to have the same pointer value?

No, that is not possible. You get a text pointer for a specific row,
and then you work with that row.

In SQL 2005 there are new data types for blobs, varchar(MAX), nvarchar(MAX)
and varbinary(MAX). You work with these just like you work with regular
varchar, and no need for the klunky UPDATETEXT. That makes life a lot lot
easier.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Artie]

Thanks for the info. I am aware of how much easier this is in SQL 2005 but
am stuck with 2000 in this case.
One more if I may...In the example syntax from BOL:

UPDATETEXT { 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 } ]

I would like to take an extire TEXT column from a source table and append it
to a TEXT column in a destination table.
Do I need to get a start/end textptr from the source?

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn*******************@127.0.0.1...

Artie (ar*******@yahoo.com) writes:

>I have a question on the UPDATETEXT function (SQL 2000)
The below query works and only updates the record where p.pub_id =
pr.pub_id.
I just don't quite understand why only 1 record is updated when the
UPDATETEXT statement
does not specify anything except pointer value. Is it no possible for 2
rows in a table with a text column to have the same pointer value?

No, that is not possible. You get a text pointer for a specific row,
and then you work with that row.

In SQL 2005 there are new data types for blobs, varchar(MAX),
nvarchar(MAX)
and varbinary(MAX). You work with these just like you work with regular
varchar, and no need for the klunky UPDATETEXT. That makes life a lot lot
easier.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Artie (ar*******@yahoo.com) writes:

Thanks for the info. I am aware of how much easier this is in SQL 2005
but am stuck with 2000 in this case.
One more if I may...In the example syntax from BOL:

UPDATETEXT { 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 } ]

I would like to take an extire TEXT column from a source table and
append it to a TEXT column in a destination table. Do I need to get a
start/end textptr from the source?

Yes, you would need a text pointer both for the source and target columns.
And you can only copy from one row to another at a time.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx