Update a value in DB with ascending values with no duplicates?

I am using this开发者_开发问答 query to update a column with ascending values:

DECLARE @counter NUMERIC(10, 0)
SET @counter = 1400000
UPDATE SomeTable
SET  @counter = SomeColumn = @counter + 1 

Question is, how can I not put duplicates there? For example the column already has 1400002 as value. Normally it has NULLs, but sometimes it doesnt. I could add

where SomeColumn is null

but this would not avoid duplicates. Any ideas?

Thanks

I am not sure that this will help or not but you can put your existing data into temp table and then use that temp table to remove duplicates like:

WHERE (@counter + 1)  not in ( select SomeColumn from #temp)

If above is not correct then please explain your question a little more.

This worked for me in SQL Server 2008:

DECLARE @StartNumber int, @EndNumber int;
SET @StartNumber = 100;
SELECT @EndNumber = @StartNumber + COUNT(*) - 1 FROM SomeTable;
WITH numbers AS (
  SELECT @StartNumber AS Value
  UNION ALL
  SELECT 
    Value + 1
  FROM numbers
  WHERE Value < @EndNumber
),
validnumbers AS (
  SELECT
    n.Value,
    rownum = ROW_NUMBER() OVER (ORDER BY n.Value)
  FROM numbers n
    LEFT JOIN SomeTable t ON n.Value = t.Value
  WHERE t.Value IS NULL
),
RowsToUpdate AS (
  SELECT
    Value,
    rownum = ROW_NUMBER() OVER (ORDER BY Value)
  FROM SomeTable
  WHERE Value IS NULL
     OR Value NOT IN (SELECT Value FROM numbers)
)
UPDATE r 
SET Value = v.Value
FROM RowsToUpdate r
  INNER JOIN validnumbers v ON v.rownum = r.rownum;

Basically, it implements the following steps:

1. Create a number table.

2. Exclude the numbers present in SomeTable.

3. Rank the rest of the rows.

4. Exclude the values from SomeTable that are present in the number table.

5. Rank the rest of the rows.

6. Update the ranked rows of SomeTable from the ranked number list.

Not sure how good this solution would be for big tables, though...