Conditionally Incrementing a Counter in a While Loop
-
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 INTDECLARE
@RandomUID TABLE ( UID INT,RandomNumber INT IDENTITY (1, 1) NOT NULL, RandomNewID uniqueidentifier,RandomDate datetime )SET @Interval = 2SET @HowMany = 10 WHILE @Counter <= @HowManyBEGINUPDATE
RandomPermTable
SET RandomDate = r.RandomDate
FROM RandomPermTable pt
INNER JOIN @RandomUID rON pt.UID = r.UID
WHERE r.RandomNumber % @Interval = 0
AND r.RandomNumber = @Counter IF @@RowCount = 1
SET @Counter = @Counter + 1 ENDThe theory behindIF @@RowCount = 1SET @Counter = @Counter + 1is 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
-
@RandomUID Table is empty therefore @@rowcount will be 0 always
* Noel -
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
-
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 -
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
-
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
-
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 -
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 -
"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.
-
- 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 -
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.
-
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 ONSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE
@HowMany INTDECLARE
@WhichCourt CHAR(4)SET @HowMany = 222SET @WhichCourt = '6180' DECLARE @Counter INTDECLARE
@RecordsRead INTDECLARE
@RecordsUpdated INTDECLARE
@DateAssigned datetimeDECLARE @Interval INTDECLARE
@CourtUnassignedCount INTDECLARE
@CourtNumberedCount INTDECLARE
@RecordKey INTDECLARE
@LastRecordKey INTDECLARE
@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 = 0SET @RecordsRead = 0SET @RecordsUpdated = 0SET @DateAssigned = GETDATE()SET @Interval = 0SET @LastRecordKey = 0 SELECT @CourtUnassignedCount = COUNT(*)FROM RandomEventsWHERE @WhichCourt = CourtAND DateAssigned IS NULL SELECT @CourtNumberedCount = COUNT(*)FROM RandomEventsWHERE @WhichCourt = CourtAND RandomNumber IS NOT NULL --Compute the highest nth interval based on how many are available and how many you wantIF @HowMany > 0BEGINSELECT
@Interval = ROUND(@CourtUnassignedCount/(@HowMany), 0, 1)ENDIF
@Interval = 0BEGINSET
@Interval = 1ENDIF
@CourtNumberedCount = 0 --we need to assign random numbers before we can continueBEGIN --give every record a unique identifier
INSERT INTO @RandomNewID (UID, RandomNewID)
SELECT UID, NEWID()
FROM RandomEvents
WHERE RandomNumber IS NULLAND
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.UIDENDIF
@CourtNumberedCount = 0 --we need to use the temp table to get the random numberBEGININSERT INTO
@RandomAssigned (UID, RandomNumber)
SELECT UID,
RandomNumber
FROM @RandomNumberENDELSE
--we need to use the random numbers in the permanent tableBEGININSERT INTO
@RandomAssigned
SELECT UID,
RandomNumber,
DateAssigned
FROM RandomEvents
WHERE Court = @WhichCourt
AND DateAssigned IS NULL END --cycle thru the available records, updating every INTERVAL oneWHILE @RecordsRead < @CourtUnassignedCountAND @RecordsUpdated < @HowManyBEGINSET
@RecordKey = (
SELECT TOP 1 RandomNumber
FROM @RandomAssigned
WHERE RandomNumber > @LastRecordKey
ORDER BY RandomNumber)
SET @RecordsRead = @RecordsRead + 1
SET @LastRecordKey = @RecordKey
IF @RecordsRead % @Interval = 0
BEGINUPDATE
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
ENDEND
-
" 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
-
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
-
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
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply