Solved

T SQL Update column with Random Passwords

Posted on 2011-03-09
4
1,159 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

Suggested Solutions

In this article I will describe the Detach & Attach 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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

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