Solved

SQL loop while field is like "x%"

Posted on 2014-02-24
6
180 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 69

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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

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 69

Accepted Solution

by:
Scott Pletcher earned 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

696 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