SQL NULL Value to = A Sequential Number, Help!

Question

I have upgraded a database at work from a poorly designed Microsoft 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. Since the existing data already has some entries that are NULL, I cannot set NOT NULL constraints.

My solution would be to use a sequential operator. For instance, NULL data would be changed to a sequential number that system administrators would know was a bogus number starting at something like 999999900, incrementing up from there. 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 it is a Hardware ID. The existing data contains both alpha and numeric data.

Any advice on how to update these NULL values using an incremental ID?

Answer 1

Another option which is worth considering in my opinion. On SQLServer you can have a filtered unique index , CREATE UNIQUE INDEX ..... WHERE column_name IS NOT NULL. Then you just need to ensure that new applications don't insert null into this column, and you don't have to generate any bogus numbers that potentially may cause conflicts with new data.

Answer 2

Well, starting with a numerical example pulled from the link at the bottom and then just changing the variable type from int to varchar() works because there is implicit conversion due to the math expression I assume. The field "id" is also a varchar().

  declare
      @start varchar(30) = 100000000;

   update t
      set @start = id = @start - 1
     from #TheTable t
    where id is null;

I came up with this mess before I realized the that the varchar() performed auto conversion. It requires a primary key (pk) but doesn't require a local variable.

  update t
     set id = r.NextId
    from #TheTable t
    join (select pk,
                 10000000 - row_number() over (order by rand()) as NextId
            from #TheTable
           where id is null) r
      on r.pk = t.pk
   where t.id is null;

(Above example was edited to fix bug after receiving comment)

Numeric Solution and many replies that may have an better answer

Update - Responding to comments: To patch them from 9's to sequential. Please double check the number of 9's, your comment listed the wrong number I think.

declare
   @fromValue varchar(30) = '9999999';  -- 7 nines

UPDATE t
   SET HardwareNumber = r.NextID 
  FROM tblAsset t
  JOIN (select Asset_ID,
               10000000 - ROW NUMBER() over (order by RAND()) AS NextID 
          FROM tblAsset 
         WHERE HardwareNumber = @fromValue) r
    on r.Asset_ID = t.Asset_ID
 WHERE t.HardwareNumber = @fromValue;

Answer 3

Hope this helps anyone with the same issue:

UPDATE r
SET HardwareNumber = r.NextID 
FROM (
   SELECT 
      999999899 + ROW NUMBER() over (order by HardwareNumber) AS NextID,
      HardwareNumber 
   FROM tblAsset 
   WHERE HardwareNumber is NULL
) r;