Adjusting a numeric col

Databases
1

Hi,
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


select 
  col1
, 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.

bazz

2

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

bazz

3

why?

4

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.

5

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

6

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?

7

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.

8

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