UPDATETEXT for multiple rows
-
Hello all. I'm trying to use UPDATETEXT to change data for text column in all rows in a table. Can't get the following to work (I've changed datatype in authors table to text and inserted XXXX in several rows). It will update the first row then loop continuously without updating. Any help appreciated.
DECLARE@TextPointervarbinary(16)
DECLARE@ReplaceStrvarchar(8000)
DECLARE@SearchStrvarchar(8000)
DECLARE@DeleteLengthint
DECLARE@OffSetint
SELECT @TextPointer = textptr(address)
FROM authors
SET @SearchStr = 'XXXX'
SET @ReplaceStr= 'YYYY'
SET @DeleteLength = len(@SearchStr)
SET @OffSet = 0
WHILE (SELECT count(*)FROM authors WHERE patindex('%'+@SearchStr+'%', address) >0) >0
BEGIN
SELECT @OffSet = patindex('%'+@SearchStr+'%', address) -1
FROM authors
WHERE patindex('%'+@SearchStr+'%', address) >0
UPDATETEXT authors.address
@TextPointer
@OffSet
@DeleteLength
@ReplaceStr
END
-
You're treating TEXTPTR() as static and it's not. Try this:
DECLARE @TextPointer varbinary(16)
DECLARE @ReplaceStr varchar(8000)
DECLARE @SearchStr varchar(8000)
DECLARE @DeleteLength int
DECLARE @OffSet int
SET @SearchStr = 'XXXX'
SET @ReplaceStr = 'YYYY'
SET @DeleteLength = LEN(@SearchStr)
WHILE EXISTS
(SELECT *
FROM Authors
WHERE Address LIKE '%'+@SearchStr+'%')
BEGIN
SELECT @TextPointer = TEXTPTR(Address), @OffSet = PATINDEX('%'+@SearchStr+'%', address) - 1
FROM Authors
WHERE Address LIKE '%'+@SearchStr+'%'
UPDATETEXT authors.address @TextPointer @OffSet @DeleteLength @ReplaceStr
END--Jonathan
Edited by - Jonathan on 11/25/2003 2:04:55 PM
--Jonathan -
Perfect. Thanks Jonathan.
Paul
-
Jonathan, nice answer.
Just a question though. Whilst it may not be important but if, for example, there were 100 rows to be updated then the select between BEGIN/END would read each row every time and update the variables 100 times then 99,98,97 etc?
Far away is close at hand in the images of elsewhere.
Anon. -
The use of TOP 1 in the SELECT(s) should speed things up a bit.
Also maybe use the @OffSet variable for the WHILE condition...
SELECT @OffSet = ...
WHILE @OffSet >= 0 ...
SELECT TOP 1 ...
END
Once you understand the BITs, all the pieces come together
-
SELECT TOP 1 certainly improved performance. Couldn't get it working using @offset for WHILE condition.
As for David's point about updating variable for each iteration, I'm not sure how this can be avoided (can't use cursor because can't declare variable as text)...
Paul
-
quote:
can't use cursor because can't declare variable as textIf the table has a PK then you could retrieve the PKs once and use a cursor to loop thru them and then action the select (using the PK instead of patindex) and updatetext.
Far away is close at hand in the images of elsewhere.
Anon. -
Another possibility is to insert the PKs into a temp table (also with PK) and then loop thru that table using 'TOP 1', select, updatetext, delete until all rows processed.
Far away is close at hand in the images of elsewhere.
Anon. -
I've got it working using a cursor (again using authors table) but I'm not sure it's exactly what you meant (still needed PATINDEX):
DECLARE @au_id varchar(30)
DECLARE @TextPointer varbinary(16)
DECLARE @ReplaceStr varchar(8000)
DECLARE @SearchStr varchar(8000)
DECLARE @DeleteLength int
DECLARE @OffSet int
DECLARE db_curs CURSOR
FOR SELECT au_id FROM authors
SET @SearchStr = 'xxxx'
SET @ReplaceStr = 'yyyy'
SET @DeleteLength = LEN(@SearchStr)
OPEN db_curs
FETCH NEXT FROM db_curs INTO @au_id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @TextPointer = TEXTPTR(Address), @OffSet = PATINDEX('%'+@SearchStr+'%', address) - 1
FROM Authors WHERE Address LIKE '%'+@SearchStr+'%'
UPDATETEXT authors.address @TextPointer @OffSet @DeleteLength @ReplaceStr
FETCH NEXT FROM db_curs INTO @au_id
END
CLOSE db_curs
DEALLOCATE db_curs
-
What I was alluding to was to create the cursor like this
DECLARE db_curs CURSOR
FOR SELECT au_id FROM authors
WHERE Address LIKE '%'+@SearchStr+'%'
to do the scan once to get the list. Then change the second select to
SELECT @TextPointer = TEXTPTR(Address), @OffSet = PATINDEX('%'+@SearchStr+'%', address) - 1
FROM Authors WHERE au_id = @au_idFar away is close at hand in the images of elsewhere.
Anon. -
Yes, I see now. Sorry for my lack of understanding. The one thing this method doesn't do that Jonathan's does is pick up multiple occurrences of the string in the same row (which I need it to do). I could keep running the script until it picked up every occurrence but that would defeat the object.
Anyway, as always, thank you all for your help and expertise.
Paul
-
quote:
pick up multiple occurrences of the string in the same rowYou could put a second WHILE inside the first to process the same row until the PATINDEX is not found.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply