... that will work, if you modify the domain constraints. but then we're back where we started :)

--- In firebird-support@yahoogroups.com, Teträm Corp <dev@...> wrote:
>
> 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
> >
> >
>