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!
