Dear all,

I have a challenge in hand and am not too sure how to accomplish this
using stored procedure. I have a table containing about 3 fields, but I
need to reorder/renumber a field value every time there is an insert or
delete on the table. Below is the table structure:

tableID customerID pageID
===== ======== ======
0 1 0
1 1 1
5 1 2
11 1 3
19 1 4
20 1 5
21 1 6
25 1 7

If I've done a delete on tableID = 11, the pageID will be reordered
(below)

tableID customerID pageID
===== ======== ======
0 1 0
1 1 1
5 1 2
19 1 3
20 1 4
21 1 5
25 1 6

Likewise, if I've done an insert on the table the stored procedure
should automatically calculate a new pageID for me and insert it into
place:

tableID customerID pageID
===== ======== ======
0 1 0
1 1 1
5 1 2
19 1 3
20 1 4
21 1 5
25 1 6
26 1 7


I have been thinking about doing this in a single stored procedure but
I am stuck with having no idea how to handle select and update in a
single SQL statement...

maybe I should do it in multiple SPs? If so, can anyone show me how? Or
should I do this outside the stored procedure in a middleware
environment where I can handle arrays easier? My concern is doing this
in middleware (PHP, ASPX, python or whatever) will hinder on speed
performance. Please advise.

Thanks.

You can have more that 1 sql statement in a stores procedure, so a
mixture of selects and updates.

Why would you want to store this counter number at all though, you can
just get the record you want out on the fly without maintaining these
numbers, for example if you wanted "page id 5" :

SELECT * FROM table ORDER BY tableID LIMIT 5,1

Godzilla wrote:
> Dear all,
>
> I have a challenge in hand and am not too sure how to accomplish this
> using stored procedure. I have a table containing about 3 fields, but I
> need to reorder/renumber a field value every time there is an insert or
> delete on the table. Below is the table structure:
>
> tableID customerID pageID
> ===== ======== ======
> 0 1 0
> 1 1 1
> 5 1 2
> 11 1 3
> 19 1 4
> 20 1 5
> 21 1 6
> 25 1 7
>
> If I've done a delete on tableID = 11, the pageID will be reordered
> (below)
>
> tableID customerID pageID
> ===== ======== ======
> 0 1 0
> 1 1 1
> 5 1 2
> 19 1 3
> 20 1 4
> 21 1 5
> 25 1 6
>
> Likewise, if I've done an insert on the table the stored procedure
> should automatically calculate a new pageID for me and insert it into
> place:
>
> tableID customerID pageID
> ===== ======== ======
> 0 1 0
> 1 1 1
> 5 1 2
> 19 1 3
> 20 1 4
> 21 1 5
> 25 1 6
> 26 1 7
>
>
> I have been thinking about doing this in a single stored procedure but
> I am stuck with having no idea how to handle select and update in a
> single SQL statement...
>
> maybe I should do it in multiple SPs? If so, can anyone show me how? Or
> should I do this outside the stored procedure in a middleware
> environment where I can handle arrays easier? My concern is doing this
> in middleware (PHP, ASPX, python or whatever) will hinder on speed
> performance. Please advise.
>
> Thanks.

Hi Paul,

Very true... I found my suggestion above a potential nightmare for tech
to maintain due to the fact that the ID will automatically updated
without their knowledge... Hence I think I will go with your suggestion
indeed. Thank you for your and have a good day!

Godzilla


paulroskilly@hotmail.com wrote:
> You can have more that 1 sql statement in a stores procedure, so a
> mixture of selects and updates.
>
> Why would you want to store this counter number at all though, you can
> just get the record you want out on the fly without maintaining these
> numbers, for example if you wanted "page id 5" :
>
> SELECT * FROM table ORDER BY tableID LIMIT 5,1
>
> Godzilla wrote:
> > Dear all,
> >
> > I have a challenge in hand and am not too sure how to accomplish this
> > using stored procedure. I have a table containing about 3 fields, but I
> > need to reorder/renumber a field value every time there is an insert or
> > delete on the table. Below is the table structure:
> >
> > tableID customerID pageID
> > ===== ======== ======
> > 0 1 0
> > 1 1 1
> > 5 1 2
> > 11 1 3
> > 19 1 4
> > 20 1 5
> > 21 1 6
> > 25 1 7
> >
> > If I've done a delete on tableID = 11, the pageID will be reordered
> > (below)
> >
> > tableID customerID pageID
> > ===== ======== ======
> > 0 1 0
> > 1 1 1
> > 5 1 2
> > 19 1 3
> > 20 1 4
> > 21 1 5
> > 25 1 6
> >
> > Likewise, if I've done an insert on the table the stored procedure
> > should automatically calculate a new pageID for me and insert it into
> > place:
> >
> > tableID customerID pageID
> > ===== ======== ======
> > 0 1 0
> > 1 1 1
> > 5 1 2
> > 19 1 3
> > 20 1 4
> > 21 1 5
> > 25 1 6
> > 26 1 7
> >
> >
> > I have been thinking about doing this in a single stored procedure but
> > I am stuck with having no idea how to handle select and update in a
> > single SQL statement...
> >
> > maybe I should do it in multiple SPs? If so, can anyone show me how? Or
> > should I do this outside the stored procedure in a middleware
> > environment where I can handle arrays easier? My concern is doing this
> > in middleware (PHP, ASPX, python or whatever) will hinder on speed
> > performance. Please advise.
> >
> > Thanks.