Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
What size does that column have? Can't you put some GUIDs (uniqueidentifier) there and just know they're bogus? –  Marian Jun 16 '12 at 8:18
    
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
1  
What version of SQL Server are you using? If you're on 2008+ then a1ex07 has a good suggestion. And what is the DDL for the table in question? Post it in your question. –  Nick Chammas Jun 17 '12 at 16:59

3 Answers 3

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.

share|improve this answer

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;
share|improve this answer
    
Ive just tried the following code: 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 99999999. –  user9435 Jun 16 '12 at 21:36
    
Sorry, I think it had worked on a trivial example but not when there are many values (I retested). If you tell me the key on the table I can fix it or else did you try the first version (you'll have to change "is null" to "= 99999999")? –  crokusek Jun 16 '12 at 22:20
    
Thank you for your help. First version I tested and I found it didn't work syntax executed fine though. The ID is Asset_ID as it is in the Asset_tbl –  user9435 Jun 17 '12 at 6:17
    
Added a version of 2nd example with your table/fields. Hopefully no errors. Please double check the number of 9's and adjust. –  crokusek Jun 17 '12 at 18:13
    
Thanks for your quick reply, I have just tried the new code and executes fine but 0 rows affected, there should be at least 250 odd. Looking at the code and I only know the basic sql nothing is saying IF HardwareNumber IS NULL then ..... –  user9435 Jun 17 '12 at 18:41

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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