How do I update X number of records, but do it one record at a time

[sumgirl]

I know its weird, please dont make me explain or defend it, but I need to update multiple records in a table with a replace and I need to do so a single record at a time. I find the following code works SOMETIMES:
-------------------x-------------------
DECLARE @CNT INT
SET @CNT = 1
WHILE(@CNT < (select max(queryid)+1 from v_CollectionRuleQuery where collectionid = 'HCT009A1'))
BEGIN
update v_CollectionRuleQuery
set QueryExpression = replace(cast(QueryExpression as nvarchar(1000)),'DDT','REMOVED')
where collectionid = 'HCT009A1' and queryID = @CNT
SET @CNT = @CNT + 1
END
-------------------x-------------------
But that works only if the [QUERYID] field I am MAX'ing to set my counter ceiling always starts at 1 and includes sequential values...which I have quickly discovered is not always the case

Very annoying! Can anyone suggest another TSQL strategy to do this? Just in case anyone is confused about the CAST thing, I found that because the [QUERYEXPRESSION] field is a text type, I could not use REPLACE() which is kind of weird, but seems to be by design.

 

[gmmastros]

I would suggest that you select your data in to a table variable first. This table variable should have an identity column. This way... you'll have a sequential ID that you can rely on.

Ex:

Declare @Temp Table(RowId Int Identity(1,1), queryId int)

Insert Into @Temp(queryId)
Select queryId
From   v_collectionRuleQuery
Where  CollectionId = 'HCT009A1'

DECLARE @CNT INT
SET @CNT = 1

While @CNT <= (Select Max(RowId) From @Temp)
  Begin

    update v_CollectionRuleQuery
    set    QueryExpression = replace(cast(QueryExpression as nvarchar(1000)),'DDT','REMOVED')
    From   v_CollectionRuleQuery
           Inner Join @Temp T
             On v_CollectionRuleQuery.queryId = T.QueryId
             And v_CollectionRuleQuery.CollectionId = 'HCT009A1'
    where T.RowId = @CNT

    SET @CNT = @CNT + 1

  End

This may not be perfect, but hopefully you get the idea. Let me know if you have any questions.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[AlexCuse]

do you have SQL 2005? If you do there is a cool ROW_NUMBER() function available that makes it so you don't need a temp table/ table var at times...

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[sumgirl]

Thanks George, that works great. To answer your question Alex, I do not yet have SQL Server 2005, but I made a note about the unique rowid stuff you mentioned, so thanks.