update sp

[aspnet98]

I have a table layed out this way:
id username value week
4 john 4 1
5 john 4 1
2 john 4 1
1 john 4 1
3 john 4 1
1 jay 4 1
3 jay 4 1
2 jay 4 1
5 jay 4 1
4 jay 4 1

Is their a way to update the first 3 records for each group (username) setting the value to a random 1 or 0 in the order I have above (I am sorting by newid() so the ids are random like you see above.


the result for above would look like this:
id username value week
4 john 1 1
5 john 1 1
2 john 0 1
1 john 4 1
3 john 4 1
1 jay 0 1
3 jay 1 1
2 jay 0 1
5 jay 4 1
4 jay 4 1

 

[DrSql]

Here is something to start with

declare @id int
declare @username char(10)
declare @vlaue int
declare @week int
declare @count int
declare @newvalue int


declare cur_count cursor for
select id,username,[value],week from TekOld
select @count=0
open cur_count
readnext:
fetch next from cur_count into
@id, @username,@vlaue,@week
If @@FETCH_STATUS <> 0
goto eof
set @count=@count+1
SELECT @NEWVALUE=CONVERT(int, (1+1)*RAND())
insert into TekNew ([COUNT],id,username,[value],week,NewValue)
values(@count,@id,@username,@vlaue,@week,@NEWVALUE)
print @usernamE
pRINT @NEWVALUE
goto readnext
eof:
--end
close cur_count
deallocate cur_count
--end
SELECT * FROM TekNew
ORDER BY USERNAME,[COUNT]
--newvalue= case [value]= from TekOld

--DELETE TekNew


Dr.Sql
Good Luck.