Click here to Skip to main content
13,898,354 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
DECLARE @intFlag INT
SET @intFlag = 1
UPDATE #temp_patrons
		set
			 @intFlag = [order] = @intFlag + 1
			 IF @intFlag = 3
			 BEGIN
			   SET @intFlag = 1
             END


What I have tried:

i tried using loop or cases but nothing happened.
The output will be like this.
EX.
| order |
|   1   |
|   2   | 
|   3   |
|   1   |
|   2   |
|   3   |
Posted
Updated 17-Feb-19 20:16pm
v3
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Can you do something like below?

1. Increment the flag
2. If flag = 3, reset to 1
3. Update operation

SET @intFlag = @intFlag + 1

	IF (@intFlag = 3)
		SET @intFlag = 1
	
	UPDATE #temp_patrons
		SET [order] = @intFlag
   
Comments
CaptainChizni 17-Feb-19 23:15pm
   
The rows are all 1. What i am going to do is increment the numbers by 1 then if it reaches 3 the increment will reset
EX.
| order |
| 1 |
| 2 |
| 3 |
| 1 |
| 2 |
| 3 |
Bryian Tan 17-Feb-19 23:40pm
   
Not clear what your query suppose to do, but I'm assuming there is a loop? I'll use Insert as an example because I'm sure there are more into it with the update statement.

DECLARE @test TABLE ([order] INT)

DECLARE @intFlag INT, @loopCount INT
SELECT @intFlag = 0, @loopCount = 0

WHILE (@loopCount < 6)
BEGIN
	SET @intFlag = @intFlag + 1

		IF (@intFlag > 3)
			SET @intFlag = 1
	
		INSERT INTO @test
			SELECT @intFlag

		SET @loopCount = @loopCount + 1
END


SELECT * FROM @test


Output:
order
1
2
3
1
2
3
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

No loops, no IF required:
UPDATE x SET x.[Order] = x.NewOrder
FROM (SELECT [Order], ((ROW_NUMBER() OVER(ORDER BY myOrderingColumnName) - 1) % 3) + 1 AS NewOrder 
      FROM MyTable) x;
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web04 | 2.8.190306.1 | Last Updated 18 Feb 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100