Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

T SQL Update column with Random Passwords

Posted on 2011-03-09
4
Medium Priority
?
1,259 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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 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