ibazz — 2011-01-12T21:22:17-05:00 — #1
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
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.
ibazz — 2011-01-12T21:24:16-05:00 — #2
basically, it is to take all sequence numbers and to reset them in a numeric fashion - keeping the sequence in the same order.
r937 — 2011-01-12T21:26:55-05:00 — #3
ibazz — 2011-01-12T21:52:13-05:00 — #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.
ibazz — 2011-01-12T21:52:53-05:00 — #5
btw, it's not an auto_increment column just a number that is meant to enforce a specific sequence.
r937 — 2011-01-12T22:18:13-05:00 — #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?
oddz — 2011-01-12T22:18:47-05:00 — #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.
furicane — 2011-01-13T04:10:31-05:00 — #8
How do you insert those integers in your sequence column? Is there some sort of a trigger or is it application controlled?