Adjusting a numeric col

I have a table column which is a INT and represents a sequence number for the output of the records from that table.

when I delete some records the seq nos will be for example, 1, 3, 7, 8, 9

I need your advice on what sort of query I need to make them again read like 1,2,3,4,5

I can identify the rows which exists and which have a sequence number

, col2
from external_links
where business_id = 2

which can return 1, 3, 7, 8, 9

How to reset them (consecutively) to 1, 2, 3, 4, 5 is what i need help with please.


basically, it is to take all sequence numbers and to reset them in a numeric fashion - keeping the sequence in the same order.



well, coz it seems that an empty number means an empty textarea on the web page and post-processing (to compensate), also seems to take a lot more effort for many web views, than does one re-adjustment of the numbers at adjustment time.

btw, it’s not an auto_increment column :slight_smile: just a number that is meant to enforce a specific sequence.

you are retrieving these numbers from a table column? and they are in sequence, although not necessarily consecutive?

is there an ORDER BY clause in your query?

Without knowing all the details that seems like a logical error/overlook on behalf of the application code. When the result set is ordered by the sequence column regardless of gaps the ordering should be the same.

How do you insert those integers in your sequence column? Is there some sort of a trigger or is it application controlled?