Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL loop while field is like "x%"

Posted on 2014-02-24
6
Medium Priority
?
196 Views
Last Modified: 2014-02-25
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
Comment
Question by:froodox
6 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39884737
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39885469
Do you want to set all of the passwords the same, or are you generating an individual password for each account?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39886207
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

Author Comment

by:froodox
ID: 39887482
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39887499
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
 

Author Comment

by:froodox
ID: 39887594
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question