> Alan:
>
> Alan McDonald escribió:
> >> Thanks Alan.
> >>
> >> What this means: "the deleted records will not change the order of the
> >> PK fields"? Shouldnt PK fields get updated at indexes as soon as they
> >> have new values?
> >>
> >> Regards
> >>
> >
> > once a record is deleted, the value of the PK is gone. The
> index will, of
> > course be updated, but the order of the records does not change
> (barring the
> > now omitted record).
> > i.e. before delete, PK values 1, 2, 3, 4, 5, 6 etc. Now delete
> record PK=3.
> > new order is 1, 2, 4, 5, 6 etc i.e. the order has not changed.
> > Alan
> >
> That´s what I´ve tought. If after delete PK=3, I want 4 -> 3, 5 -> 4,
> etc, why doesn´t "always" work? (It works most of times!).
>
> --
> Jorge Andrés Brugger

my first argument is: what's the point if the order doesn't change?
I suspect you are using this field as something user wants to manipulte and
if it's also part of a compound PK, then that's trouble IMHO.

Your original select
select codigo_producto, cantidad_pedida from items_pedidos where

>> sucursal = :sucursal and numero_pedido = :numero_pedido and
>> numero_item = :numero_item into :codigo_producto, :cantidad;

does not use anr order by clause. so you are not necessarily getting these
records back in any specific numero_item order.
think logically, to make the change you are wanting - (numero_item - 1) ,
you have to guarantee that you are not changing values to existing values.
so you need to at least order by numero_item ascending.

I still think you design is poor based on your need change (shuffle) PK
values of a table. IMHO PK values are imutable once created. Are you using
these compound fields as something meaningful to your users? That may be the
root of your problem. Are you really sure that a compound PK is a good idea?
I would argue that there is no need for it. Let the user change the value if
numero_item to anything they want as long as it's not part of a PK.
Alan