Solved

Update multiple text rows with UPDATETEXT.

Posted on 2010-11-08
7
904 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now