SQL loop while field is like "x%"

froodox
froodox used Ask the Experts™
on
Hi

I have need to run a script over certain records in a table for records that all begin with the same string (I am resetting passwords for temporary accounts).
What I have at the moment is:

Go
While User.UserName LIKE 'TEMP%'
Begin
update User
set Password='XXXX'
END

In the example I might have user accounts like TEMP1, TEMP2, TEMP3 etc

The problem seems to be in the second line.


Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you want to update all the passwords where username like TEMP1, TEMP2, TEMP3 etc


then try this

update User
set Password='XXXX'
where User.UserName LIKE 'TEMP%'
Carl TawnSenior Systems and Integration Developer

Commented:
Do you want to set all of the passwords the same, or are you generating an individual password for each account?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Don't loop!  Use a single UPDATE statement as Pratima demonstrated.

If you need to assign each user a different, generated password, code the new passwords inline or use a function in the SET statement that assigns the new password.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Author

Commented:
Hi sorry, I was wanting different passwords.  I have some code that generates random passwords, which will replace 'XXXX'.

I have applied what Pratima suggested and tried to do as Scott suggested but all of the passwords are all being set the same value.  I need them to be unique.

The expanded script is:

update User
set Password=
      substring(ch,convert(int,rand()*len(ch)-1),1)+
      substring(ch,convert(int,rand()*len(ch)-1),1)+
      substring(ch,convert(int,rand()*len(ch)-1),1)+
      substring(ch,convert(int,rand()*len(ch)-1),1)+
      substring(ch,convert(int,rand()*len(ch)-1),1)+
      substring(ch,convert(int,rand()*len(ch)-1),1)+
      substring(ch,convert(int,rand()*len(ch)-1),1)+
      substring(ch,convert(int,rand()*len(ch)-1),1)
from
      (select ch =
        replicate('ABCDEFGHJKLMNPQURSUVWXYZ',8)+
        replicate('abcdefghjkmnpqursuvwxyz',8)+
        replicate('23456789',9)+replicate('@#$^*+=<>?',7) ) a
Where User.UserName LIKE 'TEMP%'

Thanks
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
If you can deal with a 36 character password, you can use NEWID() to generate the passwords:


update User
set Password=cast(newid() as varchar(36))
Where User.UserName LIKE 'TEMP%'

Author

Commented:
Thanks
I have applied Scott's last suggestion but with a "right(###,8)" to make it more manageable, albeit more predicable but it is sufficient in this instance.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial