Re: Update violating constraint

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;

Does either of these help?

Michael Glaesemann
grzm seespotcode net