Update particular column fields with while loop in SQL Server

Last Reply 5 months ago By dharmendr

Posted 5 months ago

Hi!

I want change all login by

ddmit00000,

ddmit00001,

ddmit00002,

ddmit00003

this kind series login, but my script only by one login change all user login.

declare @result int, @i int 
set @result = (select count(*) from users)
--print @result
set @i = 0;
while @i <= @result
    begin
        update users
	set log = 'ddmit'+ right('00000' + convert(varchar(10), @i), 5)
	break
	print 'ddmit'+ right('00000' + convert(varchar(10), @i), 5)
	set @i = @i + 1;
    end;

Now I have 6 records in users table. Then I got this result

ddmit00006,ddmit00006,ddmit00006,ddmit00006,ddmit00006,ddmit00006,

Now I want below result into log cell and rows instead old login.

ddmit00000,ddmit00001,ddmit00002,ddmit00003,ddmit00004,ddmit00005

Posted 5 months ago

Hi PRA,

There is no need to use while loop.

Refer below query.

SQL

DECLARE @Test AS TABLE (ID VARCHAR(30))
INSERT INTO @Test VALUES('ddmit00006')
INSERT INTO @Test VALUES('ddmit00006')
INSERT INTO @Test VALUES('ddmit00006')
INSERT INTO @Test VALUES('ddmit00006')
INSERT INTO @Test VALUES('ddmit00006')
INSERT INTO @Test VALUES('ddmit00006')

;WITH CTE AS (
  SELECT ID, num = Row_Number() OVER (ORDER BY (SELECT NULL)) - 1
  FROM  @Test
)
UPDATE CTE SET ID = 'ddmit'+ RIGHT('00000' + CONVERT(VARCHAR(10), num), 5)
SELECT * FROM @Test

Output

    ID

ddmit00000

ddmit00001

ddmit00002

ddmit00003

ddmit00004

ddmit00005