?
Solved

SQL loop while field is like "x%"

Posted on 2014-02-24
6
Medium Priority
?
187 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

765 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