Hi,

Why use a new value from sequence ?

update tbl set order_index = -order_index where pk = :pkvalue2;

Thierry

Adam a écrit :

>
> --- In firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>, Markus Ostenried
> <macnoz@...> wrote:
> >
> > On Sat, Mar 14, 2009 at 18:23, woodsmailbox <cosmin.apreutesei@...>
> wrote:
> > >> > I know deferred constraints would solve it, but is there any
> other way in the absence of it? Assigning an out-of-domain value is
> not acceptable, since it breaks the domain definition. Thanks.
> > >>
> > >> Maybe you could swap (update) values of all other columns and
> leave the
> > >> unique column as it is?
> > >
> > > Twisted :) But I can't, since one of the other columns will always
> have a PK constraint on it.
> > >
> > > For example, the ORDER_INDEX column is used for custom ordering of
> values. I want to be able to move a row's position in the dataset
> updating that column.
> >
> > I hope I'm not missing the obvious here, but can't you assign a
> > temporary value like so:
> >
> > procedure swap (pkvalue1 integer, pkvalue2 integer)
> > as
> > variable oderindex1 integer;
> > variable oderindex2 integer;
> > begin
> > -- read original values
> > select order_index from tbl where pk = :pkvalue1 into :oderindex1;
> > select order_index from tbl where pk = :pkvalue2 into :oderindex2;
> > -- temporarily assign a magic value to one of the records
> > update tbl set order_index = MAX_INT where pk = :pkvalue2;
> > -- update both records with swapped values
> > update tbl set order_index = :oderindex2 where pk = :pkvalue1;
> > update tbl set order_index = :oderindex1 where pk = :pkvalue2;
> > end
> >
>
> In the absence of deferred constraints, you will not get an elegant
> solution to this problem.
>
> The suggested approach by Marcus might work with a bit of tweaking.
> For example, if your order items are all positive integers, you could
> create a generator and use negative numbers as your temporary holding
> variable. Your domain would need to allow negative numbers but never
> use them.
>
> In Markus' example, edit the third query to:
>
> update tbl set order_index = gen_id(gen_negativeorder_index, -1) where
> pk = :pkvalue2;
>
> This would let it work if two people attempt to swap values (of
> different sets of records) at the same time. If you can't do that
> because of the domain definition requires positive integers or because
> the data may contain negative integers, then you will just need to
> keep the transactions as brief as possible and allow for retries.
>
> In terms of avoiding server side code, you could do it with execute
> block in the newer versions of Firebird.
>
> Adam
>
>