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?

  • HI Marian, I believe the Nvarchar field size is 10 or 15, If I used GUIDs would mean changing the field size to 32 if I remember correctly, plus GUIDs are not always generated randomly although I'm sure your agree the chance of the system generating a duplicate ID String is near impossible. Good Idea this may have to be my second option. – user9435 Jun 16 '12 at 19:28

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.


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().

      @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.

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

   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;

Hope this helps anyone with the same issue:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.