T SQL Update column with Random Passwords

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
ComfortablyNumbAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
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
 
SharathData EngineerCommented:
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
 
LowfatspreadCommented:
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
 
ComfortablyNumbAuthor Commented:
Fantastic! Did the job perfectly. Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.