SQL insert random number for all NULL records in column

Hello

I have a table in MS SQL that has a field called "password" (text field) that I need to add random data to any fields that are NULL for all records in table.

I was thinking of having a number ie 1 and then for the next null field do the number +1.

so 1, 2, 3, 4 till EOF

What SQL statement can I use that will place a number while incrementing by 1 to all null values in the Password field(text field)?

Thanks for your help

FaithDan
LVL 1
FaithDanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here's an article on How to create a million sample people where I demonstrate how to use a cursor and random numbers.

>I was thinking of having a number ie 1 and then for the next null field do the number +1.
Based on this need though it should be a single UPDATE query that uses ROW_NUMBER, something like (replace YourTable, password, and ID to whatever applies to your needs)...
UPDATE YourTable
SET password = row_number
FROM YourTable
   JOIN (SELECT id, ROW_NUMBER() OVER (ORDER BY whatever) as row_number
         FROM your_table
         WHERE password IS NULL) YourTableRank ON YourTable.id = YourTableRank.ID 

Open in new window

0
Aneesh RetnakaranDatabase AdministratorCommented:
Why don't you update with NEWID() for all the nulls

update yourTable
set yourTextColumn = cast( newid() as varchar(100) )
where yourTextColumn is NULL
0
FaithDanAuthor Commented:
Hello Jim Horn,

what is YourTableRank and how do I find that

Thanks for your help

FaithDan
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I'm just using it as a table alias for the subquery, i.e. the SQL between the parentheses ( ) which acts as a table in the JOIN to YourTable.  
You can make up your own name.
0
FaithDanAuthor Commented:
JimHORN

I run this.. and get .. 0 rows affected.  But a query successful run.  What am i missing?

Thanks again for your help


UPDATE [out].[dbo].[OSUSR_VN3_STUD]
SET PASSWORD = row_number
FROM [out].[dbo].[OSUSR_VN3_STUD]
   JOIN (SELECT MASTERID, ROW_NUMBER() OVER (ORDER BY MASTERID) as row_number
         FROM [out].[dbo].[OSUSR_VN3_STUD]
         WHERE PASSWORD IS NULL) YourTableRank ON [out].[dbo].[OSUSR_VN3_STUD].id = YourTableRank.MASTERID
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Eyeballeth thy WHERE clause, ..id = ..MASTERID, should both be the same column
0
FaithDanAuthor Commented:
JimHORN  Question,  The below works (lets forget about the is NULL) how can I get the below to increment by 1 as its updateing the table when exicuted?

I really appreciate your help

faithDan


UPDATE [out].[dbo].[OSUSR_VN3_STUD]
SET PASSWORD = 'kthg6790f'
FROM [out].[dbo].[OSUSR_VN3_STUD]
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define with an example what you mean by 'incrimenting by one' in the above UPDATE.
0
FaithDanAuthor Commented:
Hello  the password can be anything.. so for the above example

SET PASSWORD = 'kthg6790f'  

the next record gets = 'kthg6790f1'
the next record gets = 'kthg6790f2'
the next record gets = 'kthg6790f3'
the next record gets = 'kthg6790f4.'   ect.... till EOF

Hope this helps clarify

Thanks

FaithDan
0
Aneesh RetnakaranDatabase AdministratorCommented:
try this way


declare @i int
set @i = 0

UPDATE [out].[dbo].[OSUSR_VN3_STUD]
SET PASSWORD = 'kthg6790f'+cast(@I as varchar), @I = @I + 1
FROM [out].[dbo].[OSUSR_VN3_STUD]
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Okay.   Just concatenate the prefix you have with the incrimenting number, casting that number as a varchar.
UPDATE [out].[dbo].[OSUSR_VN3_STUD]
SET PASSWORD = 'kthg6790f' + CAST(row_number as varchar(10))  -- <--  Looky here
FROM [out].[dbo].[OSUSR_VN3_STUD]
   JOIN (SELECT MASTERID, ROW_NUMBER() OVER (ORDER BY MASTERID) as row_number
         FROM [out].[dbo].[OSUSR_VN3_STUD]
         WHERE PASSWORD IS NULL) YourTableRank ON [out].[dbo].[OSUSR_VN3_STUD].MASTERID = YourTableRank.MASTERID 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FaithDanAuthor Commented:
Thank you so very much.

it was the IS NULL that was making my query not do anyupdates.  I changed your code at the IS NULL to   WHERE PASSWORD =''    and now it worked perfeclty.

Thank you so very much

FaithDan
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I changed your code at the IS NULL to WHERE PASSWORD =''  
A lot of times when I'm in that situation I'll use WHERE COALESCE(some_value, '') <> '', which covers both NULL and an empty string ''.

Thanks for the grade.  Good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.