Re: incrementing without violating a constraint

On 20 January 2014 15:33, Michael P. Soulier <michael_soulier(at)mitel(dot)com> wrote:
> Hi,
>
> I have a uniqueness constraint on an integer value in a table where I would
> like to mass increment all of the existing rows.
>
> ie.
> update rules set rule_number = rule_number + 1;
>
> This violates the uniquness constraint. Is there a way to say, turn off the
> constraint, run the update and then turn it back on?

You can work around this by doing the update in 2 phases:

First, update all the numbers to an increment of their value that's
guaranteed to be unique across the entire table. For example by
incrementing with the current MAX value +1.
After that, you can decrement them again so that they fall into the
desired range.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.