Solved

T SQL Update column with Random Passwords

Posted on 2011-03-09
4
1,211 Views
Last Modified: 2012-05-11
Hi Experts,

I have a table with a password column that I would like to update with new passwords. I found this query which works great but I can't seem to incorporate it with an update statement

declare @password varchar(8)
set @password=''
select @password=@password+char(n) from
(
	select top 8 number  as n from master..spt_values 
	where type='p' and number between 48 and 122
	order by newid()
) as t
select @password

Open in new window


Any suggestions?

Cheers,
Numb
0
Comment
Question by:ComfortablyNumb
[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
  • 2
4 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 35089175
Create a view like this.
create view vwRandom as select NEWID() New_ID

Open in new window

Then create a function like this.
create function dbo.fnPassword() returns varchar(20) as 
begin
declare @password varchar(8)
set @password=''
select @password=@password+char(n) from
(
	select top 8 number  as n from master..spt_values 
	where type='p' and number between 48 and 122
	order by (select New_ID from vwRandom)
) as t
return @password
end

Open in new window

Now update the password column with this statement.
update your_table set Password = dbo.fnPassword()

Open in new window

Tested on some sample data.
declare @table table (UserID varchar(10),Password varchar(10))
insert @table values ('User1','password1'),('User2','password2'),('User3','password3')
update @table set Password = dbo.fnPassword()
select * from @table

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35089179
Here is the result on my sample test.
declare @table table (UserID varchar(10),Password varchar(10))
insert @table values ('User1','password1'),('User2','password2'),('User3','password3')
select * from @table
/*
UserID	Password
User1	password1
User2	password2
User3	password3
*/
update @table set Password = dbo.fnPassword()
select * from @table
/*
UserID	Password
User1	Ipt^S72m
User2	s0dgT_RE
User3	z]w4EeYO
*/

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35089428
like this....

where pk is the primary key column of your "password" table....
update yourtable
 set password = (select [1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]
            From (select  
                 char(number) as n 
                 ,ROW_NUMBER() over (order by newid()) as rn
                 from master..spt_values 
                 cross join yourtable as x
	      where type='p' and number between 48 and 122 
	      and x.pk=yourtable.pk) as p
	      pivot (max(n) for rn in ([1],[2],[3],[4],[5],[6],[7],[8])) as pvt
	)

Open in new window

0
 

Author Closing Comment

by:ComfortablyNumb
ID: 35098283
Fantastic! Did the job perfectly. Thank you.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
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 …

705 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