update query with in while loop

See more:

SQL

server

advisor_id  advisor_name introducer_name intro_advisor_id
1            A              A
2            B              A
3            C              B
4            D              C
5            E              C
6            F              E

both name as same
they are introduce to each other

how to update the column intro_advisor_id

advisor_id  advisor_name introducer_name  intro_advisor_id
1            A              A                1
2            B              A                1
3            C              B                2
4            D              C                3
5            E              C                3
6            F              E                5

Like this i have a bulk records and i used this query

declare @advisor_id as int
declare @advisor_name as nvarchar(max)
declare @advisor_code as nvarchar(max)
set @advisor_id=1
select @advisor_id=advisor_id,@advisor_name=advisor_name from tbl_advisor_dummy where advisor_id=@advisor_id
while(@advisor_id<501)
begin
update tbl_advisor_dummy  set create_by_user_id=@advisor_id where introducer_name = @advisor_name
set @advisor_id=@advisor_id+1
end

but its not working, I am stuck with this

thanks,