Re: Update PK Violation

On Jan 16, 2008 8:30 AM, Achilleas Mantzios
<achill@matrix.gatewaynet.com> wrote:
> Στις Tuesday 15 January 2008 23:03:49 ο/η Franklin Haut έγραψε:
> > Hi all,
> >
> > i have a problem with one update sentence sql.
> >
>
> A simple way i use:
>
> foodb=# update temp set num = num*1000  where num >= 5;
> foodb=# insert into temp values (5, 'NOT');
> foodb=# update temp set num = 1 + num/1000  where num >= 6;

That's still open to possible collisions.  Another method that avoids
them is to use negative numbers.  i.e.

update temp set num = -1*num where num >=5;
insert into temp values (5,'NOT';);
update temp set num = (-1*num) + 1 where num < 0;

Assuming you don't use negative numbers in your setup, works a charm.

However, this kind of activity screams "bad design"...  Not that I've
never found myself right smack dab in the middle of such a thing