February 23, 2007 at 9:15 am
guys
I have 2 columns id and iid, I am trying to write tsql which will populate in the follow way
ID IID
1 4
2 4
3 4
4 5
5 5
6 5
7 6
8 6
9 6
basically for every 3 rows starting from 1 IID should be duplicated and for next 3 rows it should be duplicated by increment of 1
update test
set iid = 4
DECLARE @CURRENTID INT,
@CURRENTIID INT
SELECT @CURRENTID = [ID], @CURRENTIID = [IID]
FROM [test]
WHILE @CURRENTID <= MAX([id])
BEGIN
IF (@CURRENTID)/3 = 0
THEN @CURRENTIID = @CURRENTIID+1
ELSE
@CURRENTIID
END
any suggestions/inputs would help
Thanks
February 23, 2007 at 9:24 am
This takes advantage of the fact that when you perform an operation involving two integers, the result is also an integer
UPDATE MyTable
SET IID = (ID - 1)/3 + 4
John
February 23, 2007 at 9:32 am
perfect very clever and very dumb of me!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply