?
Solved

Update multiple text rows with UPDATETEXT.

Posted on 2010-11-08
7
Medium Priority
?
921 Views
Last Modified: 2012-05-10
How do I update multiple rows of text column using the Updatetext statement in SQL Server 2000
0
Comment
Question by:Bitadmin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 

Author Comment

by:Bitadmin
ID: 34089040
Here is what I've tried so far without success:

Declare @nTextPointer binary(16)

While Exists(Select * from contact1 where key1 IN('39528','41590','25228',
'41591',
'34833',
'40034',
'40725')

Begin
select @nTextPointer = TEXTPTR(notes)
from contact1
where contact1.notes LIKE  '%  Nov 10, 2010 %'

Updatetext contact1.notes @nTextPointer 76 13 ' Oct 10, 2010 '

End
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34089074
Unfortunately, the TEXT data type doesn't lend itself to bulk manipulation...
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34089178
You can loop through the table something like

Declare @nTextPointer binary(16)
declare @key1 varchar(10)
select top 1 @key1 = key1 from contact1 where key1 IN('39528','41590','25228',
'41591',
'34833',
'40034',
'40725')
while @@rowcount > 0
Begin
select @nTextPointer = TEXTPTR(notes)
from contact1
where contact1.notes LIKE  '%  Nov 10, 2010 %'

Updatetext contact1.notes @nTextPointer 76 13 ' Oct 10, 2010 '

select top 1 @key1 = key1 from contact1 where key1 IN('39528','41590','25228',
'41591',
'34833',
'40034',
'40725')
and key1 > @key1
End
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Author Comment

by:Bitadmin
ID: 34092002
I tried this but it didnot update all the rows.  

Declare @nTextPointer binary(16)
declare @key1 varchar(10)
select top 1 @key1 = key1 from contact1 where key1 IN('39528','41590','25228',
'41591',
'34833',
'40034',
'40725')

while @@rowcount > 0
Begin
select @nTextPointer = TEXTPTR(notes)
from contact1
where contact1.key1 LIKE  @key1 --'%  Oct 10, 2010 %'

Updatetext contact1.notes @nTextPointer 74 15 ' Oct 10, 2010   '

select top 1 @key1 = key1 from contact1 where key1 IN('39528','41590','25228',
'41591',
'34833',
'40034',
'40725')
and key1 > @key1
End

select key1, notes from contact1 where key1 in('39528','41590','25228',
'41591',
'34833',
'40034',
'40725')

Rows with key1 40034 and 34833 did not update.  Can you see anything in my code that is off?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34092096
Maybe check the progress?
What do the text on those records look like?
Declare @nTextPointer binary(16)
declare @key1 varchar(10)
select top 1 @key1 = key1 from contact1 where key1 IN('39528','41590','25228',
'41591',
'34833',
'40034',
'40725')

while @@rowcount > 0
Begin
select @nTextPointer = TEXTPTR(notes)
from contact1
where contact1.key1 LIKE  @key1 --'%  Oct 10, 2010 %'

select convert(varchar(100), notes) from contact1 where key1 = @key1 -- show what is being modified
Updatetext contact1.notes @nTextPointer 74 15 ' Oct 10, 2010   '

select top 1 @key1 = key1 from contact1 where key1 IN('39528','41590','25228',
'41591',
'34833',
'40034',
'40725')
and key1 > @key1
End

Open in new window

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 1000 total points
ID: 34092102
Don't worry, I see it - was missing an order by clause to make it loop properly
Declare @nTextPointer binary(16)
declare @key1 varchar(10)
select top 1 @key1 = key1 from contact1 where key1 IN('39528','41590','25228',
'41591',
'34833',
'40034',
'40725') order by key1 ASC

while @@rowcount > 0
Begin
select @nTextPointer = TEXTPTR(notes)
from contact1
where contact1.key1 LIKE  @key1 --'%  Oct 10, 2010 %'

--select convert(varchar(100), notes) from contact1 where key1 = @key1 -- show what is being modified
Updatetext contact1.notes @nTextPointer 74 15 ' Oct 10, 2010   '

select top 1 @key1 = key1 from contact1 where key1 IN('39528','41590','25228',
'41591',
'34833',
'40034',
'40725')
and key1 > @key1 order by key1 ASC
End

Open in new window

0
 

Author Closing Comment

by:Bitadmin
ID: 34095397
Thanks for all your help Cyberkiwi.  I didnot use your exact code but it gave me a similar idea using a cursor. I needed to update the text/ntext column for over 1500 records.  You get all the points and incase your curious the code I used is listed below.

USE mydatabase
GO
EXEC sp_dboption mydatabase, 'select into/bulkcopy', 'true'
GO

DECLARE @nTextpointer binary(16)
DECLARE c1 cursor for select key1 FROM contact1 where key1 IN('39528','41590','25228',
'41591',
'34833',
'40034',
'40725'.......  )

DECLARE @_id varchar(6)
DECLARE @key1 Varchar(6)

open c1
fetch c1 into @_id
while @@fetch_status = 0
begin
      SELECT @nTextpointer = TEXTPTR(notes)
      FROM contact1 WHERE key1 = @_id

      UPDATETEXT contact1.notes @nTextpointer 75 13 '% Oct 10, 2010 %'

      fetch c1 into @_id
end
close c1
deallocate c1
GO
EXEC sp_dboption mydatabase, 'select into/bulkcopy', 'false'
GO
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question