Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 937
  • Last Modified:

Update multiple text rows with UPDATETEXT.

How do I update multiple rows of text column using the Updatetext statement in SQL Server 2000
0
Bitadmin
Asked:
Bitadmin
  • 4
  • 3
1 Solution
 
BitadminAuthor Commented:
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
 
cyberkiwiCommented:
Unfortunately, the TEXT data type doesn't lend itself to bulk manipulation...
0
 
cyberkiwiCommented:
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
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.

 
BitadminAuthor Commented:
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
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
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
 
BitadminAuthor Commented:
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now