Re: Update PK Violation

On Jan 16, 2008 8:30 AM, Achilleas Mantzios
<achill(at)matrix(dot)gatewaynet(dot)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