Re: Update violating constraint

Michael Glaesemann wrote:
>
> On May 2, 2007, at 23:01 , Naz Gassiep wrote:
>
>>     I'm trying to do an update on a table that has a unique constraint
>> on the field, I need to update the table by setting field = field+1
>> however if this does not perform the updates on the table in a proper
>> order (from last to first) then the update will cause a violation of the
>> index *during* the update even though the table would be consistent
>> after the update completes.
>
> If field's values are all positive, I generally will do it in two steps:
>
> update foo
> set field = -1 * (field + 1);
> update foo
> set field = -1 * field
> where field < 0;
>
> Another way to do it is to add and then remove a large offset:
>
> update foo
> set field = 100000 * (field + 1);
> update foo
> set field = field - 100000
> where field > 100000;
Yes, in fact I actually use option one already in the handling of sql
trees, so I'm annoyed with myself for not figuring that out. I don't
know why you'd ever use your second option ever, as it virtually
guarantees problems at a random point in your DB's growth.

Thanks muchly for that!