Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Update multiple text rows with UPDATETEXT.

Posted on 2010-11-08
7
Medium Priority
?
927 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

610 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