• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

SQL loop while field is like "x%"

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
0
froodox
Asked:
froodox
1 Solution
 
Pratima PharandeCommented:
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%'
0
 
Carl TawnSystems and Integration DeveloperCommented:
Do you want to set all of the passwords the same, or are you generating an individual password for each account?
0
 
Scott PletcherSenior DBACommented:
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.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
froodoxAuthor 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
0
 
Scott PletcherSenior DBACommented:
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%'
0
 
froodoxAuthor 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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now