Forum Discussion

Angene_Thompson's avatar
Angene_Thompson
Copper Contributor
Oct 04, 2023
Solved

Updating records using while loop

The below code keeps overwriting the data even though I made the id of the record equal to the counter.

 

declare @count int;
declare @counter int;
set @count = (select count(* ) from users);
set @counter = 1;


while @counter <= @count
BEGIN
update users
set email = (select REPLACE(email,substring(email,1,charindex('@',email)-1), lower(u_name))
FROM users WHERE id = @counter)
set @counter = @counter + 1

END

  • Paula_P's avatar
    Paula_P
    Oct 09, 2023
    add the where clause again after FROM users WHERE id = @counter) :
    declare @count int;
    declare @counter int;
    set @count = (select count(* ) from users);
    set @counter = 1;


    while @counter <= @count
    BEGIN
    update users
    set email = (select REPLACE(email,substring(email,1,charindex('@',email)-1), lower(u_name))
    FROM users WHERE id = @counter)
    WHERE id = @counter
    set @counter = @counter + 1

    END

2 Replies

  • Paula_P's avatar
    Paula_P
    Copper Contributor
    Oct 09, 2023
    add the where clause again after FROM users WHERE id = @counter) :
    declare @count int;
    declare @counter int;
    set @count = (select count(* ) from users);
    set @counter = 1;


    while @counter <= @count
    BEGIN
    update users
    set email = (select REPLACE(email,substring(email,1,charindex('@',email)-1), lower(u_name))
    FROM users WHERE id = @counter)
    WHERE id = @counter
    set @counter = @counter + 1

    END

Resources