Conditionally Incrementing a Counter in a While Loop

Question

Post reply

Conditionally Incrementing a Counter in a While Loop

MattieNH

SSCertifiable

Points: 7463
More actions
July 18, 2006 at 11:44 am

#113732

Feel free to read the code below, and laugh. The problem is not the syntax, the problem is by not incrementing the counter after the first update, I never move beyond the first record.
There's nothing like posting a problem you've been working on all day to make you realize that it's time to go to lunch.
I need to update every nth record until the total number of updates matches the number of updates I need. In the example below, I have 27 records, and I need to update every 2nd record, until 10 have been updated.
Numbering the records has been the easy part. The problem is that I can't get the correct number of records updated. Here's what I have so far:
DECLARE  @Interval       INT,         @HowMany        INT DECLARE        @RandomUID      TABLE ( UID            INT, RandomNumber   INT     IDENTITY (1, 1) NOT NULL, RandomNewID    uniqueidentifier, RandomDate     datetime ) SET @Interval   = 2 SET @HowMany    = 10 WHILE  @Counter <= @HowMany BEGIN   UPDATE RandomPermTable        SET     RandomDate = r.RandomDate        FROM    RandomPermTable         pt
               INNER JOIN @RandomUID     r
               ON pt.UID = r.UID       WHERE   r.RandomNumber % @Interval = 0        AND     r.RandomNumber = @Counter IF     @@RowCount = 1
       SET @Counter = @Counter + 1         END

The theory behind
IF @@RowCount = 1
SET @Counter = @Counter + 1
is to only increment the counter controlling the loop if a record has been updated successfully.
There's obviously something about the syntax of the WHILE statement that I'm not getting, because this logic sends it into an infinite loop (or runs a whole lot longer than I'm will to wait for). I'd like to know what I'm doing wrong with the structure above, but if anyone can come up with a better solution, I'd be just as happy.
Thanks,
Mattie

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

@RandomUID Table is empty therefore @@rowcount will be 0 always

* Noel

MattieNH SSCertifiable Points: 7463 More actions · Answer 2

Well, it's a dumb mistake, but not that dumb.

I just included the definition of @RandomUID table in case there was something in there that pertained to what I thought my problem was. There's a whole slew of code that precedes the logic that was giving me trouble that I didn't bother to include.

But I appreciate you looking at the code. Because the second part of my question still pertains: is there a better way of doing the conditional update.

Thanks,

Mattie

stax68 SSChampion Points: 11711 More actions · Answer 3

Do you expect a lot of failed updates? Do you need to do them as a series of individual transactions? If not, why not just do teh updates as a single statement. If you expect errors, you should check for the presence of the error-generating conditions before updating.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

MattieNH SSCertifiable Points: 7463 More actions · Answer 4

Tim,

Do you expect a lot of failed updates? Do you need to do them as a series of individual transactions?

No, I expect no failed updates.

I think I need them as a series of individual transactions because I can't assign more than requested. When I tried doing it as a single update, my logic with needing every other record out of 27, until I had 10 records, ended up updating 13 records. I need a way to say 'enough' after 10.

Mattie

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 5

WHERE r.RandomNumber % @Interval = 0

AND r.RandomNumber = @Counter

equals

WHERE @Counter % @Interval = 0

AND r.RandomNumber = @Counter

Q1 - Which is the value of @Counter ? (hopefully not NULL : )

Q2 - WHY " AND r.randomNumber=@Counter" from your description you don t wont this. If @Counter=1 or 0 then your loop will go forever : )

Q3 - WHY not POST initial DD and result wanted

Kindest Regards,

Vasc

stax68 SSChampion Points: 11711 More actions · Answer 6

If it doesn't matter which 10 you update,

update top 10 n
set c = 'x'
from tablen n
where id % 2 = 0

if it does;

update n

set c = 'x'

from tablen n

join (select top 10 id --[, any other cols needed in update...]

from tablen n

where id % 2 = 0) v

on n.id = v.id

or

update n

set c = 'x'

from tablen n

where n.id in

(select top 10 id

from tablen n

where id % 2 = 0)

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Sergiy SSC Guru Points: 110206 More actions · Answer 7

The problem is you expect no failed updates and you have not decided what to do when it's happened.

Now you just repeating failing update again and again. Not the smartest approach, as for me.

_____________
Code for TallyGenerator

Vladan SSC-Insane Points: 22072 More actions · Answer 8

"I need a way to say 'enough' after 10."

Have you considered SET ROWCOUNT?

I'm not saying this is the best way to go, but it surely stops you after required number of updates (while updating set-based, not row by row).

DECLARE @Interval int

SET @Interval = 5

DECLARE @HowMany int

SET @howmany=3

SET ROWCOUNT @howmany

UPDATE RandomPermTable

SET RandomDate = '20060606' /*simplified - add tables and conditions as necessary*/

WHERE RandomNumber % @interval = 0

SET ROWCOUNT 0

I think there should be better solution, but since I'm not sure what you want to do, why, and what is the real data structure and values, I have no idea how to do it.

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 9

Have a go with this.

declare @i int

set @i = 0

update RandomPermTable

set @i = @i + 1, RandomDate = case when @i <= 10 then r.RandomDate else pt.RandomDate end

FROM RandomPermTable pt

INNER JOIN @RandomUID r

ON pt.UID = r.UID WHERE r.RandomNumber % @Interval = 0

AND r.RandomNumber = @Counter

www.sql-library.com[/url]

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 10

Forgot the interval bit

declare

@i int, @interval int

set

@i = 0, @interval = 0

update

RandomPermTable

set

@i = @i + 1,@interval = @interval +1, RandomDate = case when @i <= 20 and @interval % 2 = 0 then r.RandomDate else pt.RandomDate end

FROM

RandomPermTable pt

INNER

JOIN @RandomUID r

ON pt.UID = r.UID

WHERE r.RandomNumber % @Interval = 0

AND r.RandomNumber = @Counter

Havnt tested this but something along these lines should work.

www.sql-library.com[/url]

MattieNH SSCertifiable Points: 7463 More actions · Answer 11

First of all, I want to thank everyone who took time to look at this issue. I'm truly impressed at how you all can even attempt to come up with a solution without knowing the underlying business problem.

I wasn't born a procedural language programmer, but I will die one. So posted below is the solution that I came up with, that does exactly what I want. It allows me to update the AssignedDate on every nth (defined as Interval) record, and it lets me do it multiple times.

Now, to your specific suggestions. Vladan, you are correct, to use Tim's suggestions, I would have had to use SET ROWCOUNT, because the TOP value is a user-supplied variable.

Tim, thanks for those statements. I especially appreciated the two statements that do a terrific job of showing how a query using a subquery equates to a query using a derived table. It strikes me that the problem I'm going to have with your code (and my original code) is that it won't work with multiple iterations; that I'm going to run out of eligible events before I have enough assigned.

Jules, I'm also going to try and incorporate your logic. I think your first solution was closer, because Interval doesn't need to be incremented.

And Sergiy, I appreciate your comments as well, and I hope you haven't read ahead yet to my current solution and had a heart attack over its procedural nature. As to your point about not expecting any failed updates, I don't really consider an infinite loop a failed update, I consider it a programming error during the design stage.

And it's not that I don't consider the possibility of errors occurring. As a general rule, when I'm writing a stored procedure, I first focus on getting it to do what I want it to, assuming no input errors, a stable data source, and all those other things that so rarely occur every time in production. Then, once it works under ideal conditions, I start to consider what would happen if they weren't all present. I'm truly curious to know how you work into your solutions.

Here's my current solution, with thanks to SQL Prettifier (http://www.simple-talk.com/prettifier/).

DDF for permanent table being updated:

CREATE TABLE [dbo].[RandomEvents]

[UID] [int] IDENTITY (1, 1) NOT NULL ,

[RandomNumber] [int] NULL ,

[DateAssigned] [datetime] NULL ,

[Court] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[ServiceDate] [datetime] NOT NULL

T-SQL that will end up as a stored procedure:

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
DECLARE @HowMany        INT
DECLARE        @WhichCourt     CHAR(4)
SET    @HowMany        = 222
SET     @WhichCourt = '6180' 
DECLARE        @Counter                INT     
DECLARE        @RecordsRead            INT
DECLARE        @RecordsUpdated         INT
DECLARE @DateAssigned           datetime
DECLARE @Interval               INT
DECLARE @CourtUnassignedCount   INT
DECLARE @CourtNumberedCount     INT
DECLARE @RecordKey              INT
DECLARE @LastRecordKey          INT 
DECLARE        @RandomNewID    TABLE   
(
       UID             INT,
       RandomNewID     uniqueidentifier
) 
DECLARE        @RandomNumber   TABLE   
(
       UID             INT,
       RandomNumber    INT     IDENTITY (1, 1) NOT NULL,
       RandomNewID     uniqueidentifier
) 
DECLARE        @RandomAssigned TABLE   
(
       UID             INT,
       RandomNumber    INT,
       DateAssigned    datetime
) 
SET    @Counter        = 0
SET    @RecordsRead    = 0
SET    @RecordsUpdated = 0
SET @DateAssigned       = GETDATE()
SET    @Interval       = 0
SET    @LastRecordKey  = 0 
SELECT         @CourtUnassignedCount = COUNT(*)
FROM   RandomEvents
WHERE  @WhichCourt = Court
AND    DateAssigned IS NULL       
SELECT         @CourtNumberedCount = COUNT(*)
FROM   RandomEvents
WHERE  @WhichCourt = Court
AND    RandomNumber IS NOT NULL 
--Compute the highest nth interval based on how many are available and how many you want
IF     @HowMany > 0
BEGIN
SELECT @Interval = ROUND(@CourtUnassignedCount/(@HowMany), 0, 1)
END 
IF     @Interval = 0
BEGIN
       SET @Interval = 1
END     
IF     @CourtNumberedCount = 0   --we need to assign random numbers before we can continue
BEGIN  
--give every record a unique identifier
       INSERT INTO @RandomNewID (UID, RandomNewID)
               SELECT  UID, NEWID()
               FROM    RandomEvents
               WHERE   RandomNumber IS NULL      
               AND     Court = @WhichCourt 
--give every record a sequence number based on the unique identifier
       INSERT INTO @RandomNumber
               SELECT  UID,
                       RandomNewID
               FROM    @RandomNewID
               ORDER BY RandomNewID 
--tell the records in the permanent table what their sequence number is
       UPDATE  RandomEvents
               SET     RandomNumber    = r.RandomNumber
               FROM    RandomEvents                    re
                       INNER JOIN @RandomNumber  r
                               ON      re.UID = r.UID
END     
IF     @CourtNumberedCount = 0   --we need to use the temp table to get the random number
BEGIN
       INSERT INTO @RandomAssigned (UID, RandomNumber)
               SELECT  UID,
                       RandomNumber
               FROM    @RandomNumber
END    
ELSE --we need to use the random numbers in the permanent table
BEGIN  
       INSERT INTO @RandomAssigned
               SELECT  UID,
                       RandomNumber,
                       DateAssigned
               FROM    RandomEvents
               WHERE   Court = @WhichCourt
               AND     DateAssigned IS NULL      
END     
--cycle thru the available records, updating every INTERVAL one
WHILE  @RecordsRead < @CourtUnassignedCount
AND    @RecordsUpdated < @HowMany
BEGIN  
       SET     @RecordKey = (
               SELECT  TOP 1 RandomNumber
               FROM    @RandomAssigned
               WHERE   RandomNumber > @LastRecordKey
               ORDER BY RandomNumber) 
       SET     @RecordsRead    = @RecordsRead + 1
       SET     @LastRecordKey  = @RecordKey
       
       IF      @RecordsRead % @Interval = 0
       BEGIN   
               UPDATE  RandomEvents
                       SET     DateAssigned = @DateAssigned
                       FROM    RandomEvents    re
                       INNER JOIN       @RandomAssigned r
                               ON      r.UID = re.UID
                       WHERE   r.RandomNumber = @RecordKey
                       AND     re.DateAssigned IS NULL 
               SET @RecordsUpdated = @RecordsUpdated + 1
       END

END

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 12

" As to your point about not expecting any failed updates, I don't really consider an infinite loop a failed update" -I gave you the reason why this happened

The solution which ITERATES through a possible LARGE no of records is just NOT FROM A DB programmer (I HOPE ). You should try and elaborate on this (a variation of Vladan s solution @Interval<@TotalRows):

DECLARE @Interval int

SET @Interval = 5

DECLARE @howmany int

SET @howmany=3

DECLARE @TotalRows int

DECLARE @OneTime int

DECLARE @Cicles int

SELECT @TotalRows=COUNT(*) FROM RandomPermTable pt INNER JOIN @RandomUID r ON pt.UID = r.UID

IF @TotalRows<@HowMany

BEGIN

--update all rows

UPDATE RandomPermTable

SET RandomDate = r.RandomDate

FROM RandomPermTable pt

INNER JOIN @RandomUID r ON pt.UID = r.UID

END

ELSE

BEGIN

--determine how many FULL cicles are needed to update the desired number of rows

SET @Cicles=(@Interval*@HowMany)/@TotalRows

If @Ciles>0

UPDATE RandomPermTable

SET RandomDate = r.RandomDate

FROM RandomPermTable pt

INNER JOIN @RandomUID r ON pt.UID = r.UID

WHERE r.RandomNumber % @Interval <@Cicles

--update the remaining ones

SET @NoOfRows=@HowMany-@Cicles*(@TotalRows/@interval)

SET ROWCOUNT @NoOfRows

UPDATE RandomPermTable

SET RandomDate = r.RandomDate

FROM RandomPermTable pt

INNER JOIN @RandomUID r ON pt.UID = r.UID

WHERE r.RandomNumber % @Interval =@Cicles

END

NOTE: there are no cicles and is SET based

MIGHT not be what you want since you didn t post the DDL and sample data with desired result but you can get the idea behind

Kindest Regards,

Vasc

MattieNH SSCertifiable Points: 7463 More actions · Answer 13

Vasc,

Thank you for the time you've taken on this. I've tried to take your logic, but I'm having trouble with the variable @Cycles. In the formula,

SET @Cycles = @Interval * @howmany / @TotalRows

@Cycles will almost always be zero, unless the last record falls at the end of an interval, in which case @Cycle will be 1.

So here's my definition of what the fields are:

@Interval is the gap between assigned rows; every nth row

@howmany is the number of rows that require an assigned date

@TotalRows is the number of rows available to have a date assigned

Here's some data that may illustrate what I want. I have 27 rows available, and I need 10 assigned, creating an interval of 2. This is what the data looks like after a successful run:

UID RandomNumber DateAssigned

----------- ------------ ------------------------------------------------------

27223 1 NULL

27463 2 2006-07-20 09:59:29.390

27461 3 NULL

27462 4 2006-07-20 09:59:29.390

27222 5 NULL

27458 6 2006-07-20 09:59:29.390

28337 7 NULL

28342 8 2006-07-20 09:59:29.390

28421 9 NULL

27600 10 2006-07-20 09:59:29.390

28341 11 NULL

27460 12 2006-07-20 09:59:29.390

28335 13 NULL

27217 14 2006-07-20 09:59:29.390

27459 15 NULL

27457 16 2006-07-20 09:59:29.390

27215 17 NULL

27599 18 2006-07-20 09:59:29.390

27218 19 NULL

28334 20 2006-07-20 09:59:29.390

28339 21 NULL

27216 22 NULL

27214 23 NULL

27213 24 NULL

27219 25 NULL

28340 26 NULL

27212 27 NULL

Then the user decides he need 5 more assigned. I have 17 remaining rows, giving me an interval of 3. Here's what the data looks like after that run:

UID RandomNumber DateAssigned

----------- ------------ ------------------------------------------------------

27223 1 NULL

27463 2 2006-07-20 09:59:29.390

27461 3 NULL

27462 4 2006-07-20 09:59:29.390

27222 5 2006-07-20 10:32:39.187

27458 6 2006-07-20 09:59:29.390

28337 7 NULL

28342 8 2006-07-20 09:59:29.390

28421 9 NULL

27600 10 2006-07-20 09:59:29.390

28341 11 2006-07-20 10:32:39.187

27460 12 2006-07-20 09:59:29.390

28335 13 NULL

27217 14 2006-07-20 09:59:29.390

27459 15 NULL

27457 16 2006-07-20 09:59:29.390

27215 17 2006-07-20 10:32:39.187

27599 18 2006-07-20 09:59:29.390

27218 19 NULL

28334 20 2006-07-20 09:59:29.390

28339 21 NULL

27216 22 2006-07-20 10:32:39.187

27214 23 NULL

27213 24 NULL

27219 25 2006-07-20 10:32:39.187

28340 26 NULL

27212 27 NULL

You can tell by the times which run the date was assigned in.

I really want to make this set-based rather than procedural, so I appreciate you taking a look at this. And at least I didn't use a cursor.

Thanks,

Mattie

stax68 SSChampion Points: 11711 More actions · Answer 14

I can't work out what you are actually trying to achieve. What is the logic behind all these cycles and random numbers? It seems overcomplicated. Is the approach dictated by logical or physical constraints?

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant