simple incrementing? 1

[Lahorem]

I have a table with 4 rows.. id..xx..yy..zz..
now out of the 4000 rows, some data has been taken out in the middle..eg:

220 shares money casino
221 shares2 penny dollar
225 stock hello moto
229 sucks help me
230 etc eg iou


I am slightly confused as to how I would go about sorting the order for this. What kind of an update or alter statement can i use for this?
Thanks in advance for your help

 

[r937]

I am slightly confused as to how I would go about sorting the order for this

i am confused as to what you are asking

r937.com | rudy.ca

 

[Lahorem]

ok apologies .. i ll rephrase
I basically want to reorder the id field so it goes like:

220
221
222
223
....

as apposed to what it is right now (see above). I am not sure how I can use an update or an alter command for this operation?

Hope that clears it?

 

[r937]

and why do you want to reorder the id field?

if this is a typical id field, the gaps shouldn't matter, and if the gaps do matter, then maybe you could explain why

r937.com | rudy.ca

 

[Lahorem]

your right but this data is later processed up by a program further down the chain which requires it to be in a consecutive order hence the question.
Thanks

 

[r937]

well, there are lots of solutions, pick one

- drop the id field and add it back as IDENTITY

- selectively update the ids

update daTable set id=222 where id=225
update daTable set id=223 where id=229
update daTable set id=224 where id=230
...

- rewrite the downstream program so that it doesn't care if the ids are consecutive




r937.com | rudy.ca

 

[vongrunt]

update blah
set id = 
(	select min(id) + count(*) -1
	from blah B2
	where B2.id <= blah.id
)

blah = daTable

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[Lahorem]

Many thanks guys! certainly helped

 

[r937]

vongrunt, again with the starworthy reply!!



r937.com | rudy.ca