Subject | Re: [firebird-support] Re: how to swap two values on a uniquely constrained column? |
---|---|

Author | Teträm Corp |

Post date | 2009-03-15T08:25:09Z |

Hi,

Why use a new value from sequence ?

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

Thierry

Adam a écrit :

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

>

>