I have a table with an ID 1 - 100. I want to insert a repeating 1, 2, 3, 4 in ascending order into the second column. I need to use a variable for the starting number (1, 2, 3 or 4). I tried using Sequence but it doesn't allow variables. How can this be accomplished?

CodePudding user response：

You can use the modulus operator % for that. Modulus operations are zero-based, so there is some adjustment needed to get the result into the one-based regime you need.

If the table is named "SO70806943", the ID column is named "ID", and the column for the numbers 1–4 is named "OneToFour":

DECLARE @StartingNumber INT;
SET @StartingNumber = 3; /* For example */

UPDATE SO70806943
SET OneToFour = (ID   @StartingNumber - 2) % 4   1

Before:

ID  OneToFour
1   0
2   0
3   0
4   0
5   0
6   0
7   0
8   0
9   0
10  0

After:

ID  OneToFour
1   3
2   4
3   1
4   2
5   3
6   4
7   1
8   2
9   3
10  4

CodePudding user response：

Another update that uses the modulus 4 of the ID.
But by changing the resulting 0 to 4.

declare @start int = 2;
update your_table set col = isnull(nullif(id%4 @start-1, 0), 4);

select top 10 * from your_table order by id;

Test db<>fiddle here