ComfortablyNumb
asked on
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
Any suggestions?
Cheers,
Numb
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
Any suggestions?
Cheers,
Numb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
like this....
where pk is the primary key column of your "password" table....
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
)
ASKER
Fantastic! Did the job perfectly. Thank you.
Open in new window