Re: SQL Alter table: can I remove the UNIQUE attribute from a key?

On 02 May 2013 10:56, rob@xxxxxxxxx wrote:

I suspect he's still battling with trying to re-sequence the file
from a previous thread and he's hitting a duplicate key before he
gets to the duplicate and is able to renumber it.

Although, unless his old keys started with less than 1, or use less
than whole number increments, if he's reading the file in key order,
I don't understand how he'd hit the duplicate. Perhaps he's
re-sequencing based on some other column.

I was pretty sure I had already described why the re-sequencing problem for an existing column as presented in a recent thread started by Joel [Subject: update column with a sequential #] is not generically probable to accomplish without duplicate key errors; i.e. the capability is data dependent, without either using isolation other than *NONE or having a UNIQUE WHERE NOT NULL INDEX rather than the typical UNIQUE INDEX.
http://archive.midrange.com/midrange-l/201304/msg01411.html
http://archive.midrange.com/midrange-l/201304/msg01410.html

With some /assumptions/ about the data, then there are some simple means to effect similar to the two-pass method shown here where the unique index on The_Column is a UNIQUE WHERE NOT NULL INDEX:
UPDATE The_File Set The_Column = Cast(NULL as The_Column_Type)
UPDATE The_File Set The_Column = Next Value For The_New_Sequence

For example assume all sequence numbers for The_Column are all positive, the first UPDATE can set the current value to negative values using multiplication by negative one:
UPDATE The_File Set The_Column = (-1 * The_Column_Type)
UPDATE The_File Set The_Column = Next Value For The_New_Sequence

Obviously the above negative\all-positive sequence values could be done with a file that is or has since been set to have the sequence match the RRN().

Choosing a low value must ensure there are enough values between that value and the lowest existing value, and choosing a high value one larger than the highest existing value must take care to know that the new values will not overflow given the number of rows having the re-sequenced value. As I noted... data-dependent solutions.

What I offered were not data dependent or at least not directly; i.e. the use of the WITH UR isolation clause limits the amount of change to however many rows can be changed under isolation without a commit, so it is dependent on the amount of data to be changed\resequenced.