Solved

Update multiple text rows with UPDATETEXT.

Posted on 2010-11-08
7
915 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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 250 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 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