update sequential numbers in order using while loop 1

[fredong]

HI
I need to update A table B column with squential numbers in the order of 1,2,3,4 5 and so forth. Currently, the B column has the of 2,5,7,8, 15 and so forth. Please advise. Thanks

 

[SQLBill]

What kind of column is it? INTEGER? IDENTITY?

-SQLBill

Posting advice: FAQ481-4875

 

[SQLSister]

If this number is a identifier of any sort, this would be an extremely bad idea as you could destroy data integrity in related tables.

Questions about posting. See faq183-874

 

[fredong]

is an integer but not an identity column.

 

[AlexCuse]

You would do well (as usual) to heed the advice of SQLSister on this. If it is an identifier to be used in queries across different tables, then you might as well drop the database instead of running this query, because either way I see a restore from backup in your near future. So make sure to quadruple check this!

So if you have duplicates in colB, this will not work. If you have zero in colB, this also will not work. Check these carefully. Run these queries to check:

select colB, count(*) 
from myTable
group by colB
having count(*) > 1

select * from myTable where colB = 0

if either of these return a resultset, read no further!

Now that the disclaimer is done, here is a query that will loop through, grab the lowest ID (that is >= the incrementing counter used to assign), and update it with a counter.

--set up test data
declare @test table (colB int primary key clustered, val varchar(2))

insert into @test
select 5, 'PA'
union all select 3, 'MS'
union all select 19, 'KB'

--check pre-update
select * from @test

--establish variables used in update, initialize count
declare @cnt int, @minID int
set @cnt = 1

--loop through and update
while @cnt <= (select count(*) from @test)
begin
	--get the lowest ID that is >= counter, in order to identify record to update
	select @minID = min(colB) from @test where colB >= @cnt	

	--update the record
	update @test
	set colB = @cnt
	where colB = @minID

	--increment the counter
	set @cnt = @cnt + 1
end

--check post-update
select * from @test

MAKE SURE that running this query is not going to screw you over however. Because like I (and others) have said, there is tremendous potential for damage here.


Good Luck,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[fredong]

the initial COl B came in as unique without constraint and no zeros as well. Eventually it has to allow duplicates numbers this is because I have 2 filenames in COL C in the A table and I need to split it into 2 order numbers starting with 1,2,3,4 and so forth. I ran the script you wrote without the primary key clustered to update the first file (where COL C ='PP.txt') and I got duplicate? Please advise. Thanks.

 

[AlexCuse]

Can you post the query you ran to update it? If you modified it correctly there should not be any duplicates (unless there were duplicates to begin with) My initial thought is that you tried setting where COL C = 'pp.txt' and ignored the condition where colB = @minID. OR you failed to add the 'pp.txt' piece. So please, post the exact query that you ran.

The fact that you are trying to use 2 different sequences that may contain duplicates should definitely have been mentioned in your initial post. This could have been planned for, but nobody but yourself was aware of the need. Please read the FAQs in SQLSister's signature.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[fredong]

I only try to update the first file pp.txt and it already given me a duplicate numbers of

Number
1
1
2
2
3
4
5

Bear in the Number COL also known as COl B earler it started with unique numbers and no zeros and it started with 1, 2, 3 and so forth and I am trying to split the file into pp.txt and qq.txt and rearrange their numbers in order again sequentially.

Here is my code below.

declare @ResetNum327 int, @min327Num int
set @ResetNum327 = 1

--loop through and update
while @ResetNum327 <= (Select count(*) from A_Table(NOLOCK) where code ='40' and filename PP.txt)
begin
--get the lowest ID that is >= counter, in order to identify record to update
select @min327Num = MIN(Number) from A_Table(NOLOCK) where code ='40' and filename PP.txt and Number >= @ResetNum327

--update the record
update A_Table
set Number = @ResetNum327
where Number = @min327Num

--increment the counter
set @ResetNum327 = @ResetNum327 + 1
end

Thanks.

 

[AlexCuse]

Do you see this?

where code ='40' and filename PP.txt and Number >= @ResetNum327

used when getting your min ID to update?

When you actually run the update query, these conditions should be applied as well. You are assigning the new number to every row with Number = @min327num, and you should be assigning where Number = @min327Num AND proper code and filename are present.....

So try replacing your query with this:

    update A_Table
    set Number = @ResetNum327
    where Number = @min327Num 
    and code ='40' 
    and filename = 'PP.txt'

Notice the change I made to your file name condition as well (from filename PP.txt to filename = 'PP.txt')

You will of course need to adjust this and run for your other file name. Does this make sense? Post back if you have any questions.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[fredong]

Hi Alex,
Thank you very much for your help. It works for me. God bless you.

 

[SQLBill]

fredong,

Alex gave you a lot of help. If you want to show your appreciation, at the bottom of the post you will see:

Thank AlexCuse
for this valuable post!

Click on that link and follow the directions to award Alex a star.

-SQLBill

Posting advice: FAQ481-4875

 

[fredong]

I have thanked him for the valuable post.