Null values to = unique ID to allow No duplicates

[rustynails28]

Ok I have upgraded my works database from a poorly designed Access database to a SQL database. The previous system allowed NULL values and duplicates to be inserted into a field that should NOT ALLOW NULL Values or duplicates. Therefore, this issue has now been moved across to my new system as I cannot set these constraints on the field that has multiple NULL values.

My solution would be to use a sequential operator, so whatever = NULL would be changed to a sequential number that us as administrators would know was a bogus number starting at something like = 999999900 counting up from that. There are only 250 records that would require updating.

To make things more interesting this field is not a integer type, its a Varchar type as its a Hardware ID. Both numerical and characters are require.

I cannot get my code to work

UPDATE tblAsset SET HardwareNumber = r.NextID FROM tblAsset cross join (select 10000000 - ROW NUMBER() over (order by RAND()) AS NextID FROM tblAsset WHERE HardwareNumber is NULL) r WHERE tbl.Asset.HardwareNumber is NULL - This code only changes all NULL values in the HardwareID field to 99999900.

I want the NULL values to start at 99999900 at go up in an increment (1) each record so that there is no duplicates.

 

[Qik3Coder]

Do you have anything in the table that is unique to the 250 rows? Even something like a date/time? You need to be able to iterate over the list and pull the next one. Without a specific key, you won't be able to update a single row at a time.

You may havy to resort to adding a new column of type int, updating it with a ROW_Number and then doing your update that way.

Lodlaiden

You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)

 

[djj55]

Since this is a one time move I would create a new table with only the NULL HardwareNumber records and an extra INT column. Populate the INT column as desired. Then update with the extra column to fill in the NULL values of HardwareNumber using something like CAST(AdditionalColumn AS VARCHAR(10)).

I hope this at least gives you an idea.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!