keeping ids consecutive after removing data 1

[Lahorem]

I currently have a table where I have to remove the type1 data (see below)

id type

1 type1
2 type1
3 type1
5 type1
7 type1
8 type2
11 type3
14 type2
15 type2
16 type2
17 type2
...
.....

Now I have removed the type1 data using

update table statements

but I need to have the id's reset too. By that I mean that ids need to be kep consecutive. So with the type1 rows taken out..the ids need to be moved down.

Question: I am stuck on how I would go about doing this as my skills with SQL are somewhat basic?

Many thanks in advance guys

 

[SimonSellick]

Are there gaps between type 1 rows, or are all the type 1 ids in a single chunk?

If there are no gaps, you could renumber in situ:

1) find the lowest (L) and highest (H) id that you plan to delete
2) calculate the difference between them (D)
3) delete the rows with ids between L and H
4) update each remaining row whose id exceeds H, subtracting D + 1 from its id

This can be done in straight SQL with a query, a delete and an update.

If there are gaps between the ids, then you could still apply the above rules, but you would have to repeat the process for each contiguous set of ids to be deleted - in that case, you would be best off with a stored procedure to do the work.

If you are happy to use a temporary table, then another way would be to delete the unwanted rows then copy the remaining ones into a new temporary table with an identity column. Truncate the original table then copy all the rows from the temporary table back into the original, taking the identity column value as the id.

HTH

 

[jbenson001]

Why do you need to keep them consecutive? If it is a true ID, then it should never be changed, especially if it is related in anyway to other tables.


Jim

 

[Lahorem]

Thanks for your input SimonSellick .. Though I am not sure how I can use delete and update to go through all the rows (around 3000 rows) and update the ids?

 

[SQLSister]

LAhorem JBenson is correct, under no circumstances should you ever consider changing the id field once set. You will end up creating data integrity problems sooner or later. If you have related tables, you must either have cascade update set or you must update the tables manually saving the old and new id fields or your data will be hopelessly mixed up. Further renumbering all data every time one is deleted puts an unnecessary workload on the system which is bad now but will become much worse as the number of records in the table increase. This will end up slowing all your processing if it happens frequently.

Additionally, you may confused users who are looking for a particluar record and who have the old number but the not hte new one. Again they will enter new information against the wrong record by accident messing up data integrity again or they will insist the record has been delted and you will spend hours trying to find data for them.

There is no circumstance in which this is a good idea.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[jby1]

I cannot think of any circumstances under which this could be useful.

If you need a consecutive numbering of records, could you not create another column in the table, which is not an id field. Then do your consecutive number on that field?

Would be helpful if you explained why you think you need to do this though, cos there is a good chance that somebody will be able to suggest a better way of acheiving your aims.

 

[Lahorem]

thanks for all your input guys / girls

Something I should have mentioned before is that id field is not used to identify unique numbers, which is why I was asking what syntax could I use to delete and update to go through all the rows and update the ids field???

so to expand..there is a seperate pimary key in the table that has unique set of data which i do not plan on changing to avoid dat aintegrity problems.
So any ideas on the sql I could use?
Thanks in advance.

 

[SimonSellick]

Lahorem,

If you've identified a block of rows with contiguous ids to delete (say ids L...H from my earlier post), then you can delete them with:

delete from tbl where id between L and H

and update the succeeding ones with:

update tbl
set id = id - (H - L + 1)
where id > H

You would have to do this for each consecutive block identified for deletion.

I must agree with Jim, jby and SQLSister - this is an unusual requirement. Perhaps 'id' is a poor choice of name for this column?

HTH
Simon

 

[Lahorem]

yes I agree id is a poor choice of name.

Anyway thanks for that Simon but on running that all of the ids have the same number from where the deletion takes place

so if i deleted id = 2 where type = type 1 then the rest of ids are turned into 3.
Any ideas? :-s

 

[SimonSellick]

Bad update statement I should think. Can you post the statement that you used?

 

[Lahorem]

nop nop no worries

finally figured it out
cheers anyway