Re: Updating a sequential range of unique values?

On Fri, Feb 17, 2006 at 08:07:26PM -0800, Benjamin Smith wrote:
> How can I update a range of constrained values in order, without having to
> resubmit a query for every single possiblity?
>
> I'm trying to create a customer-specific sequence number, so that, for each
> customer, the number starts at one, and continues, 1, 2, 3, 4, 5... etc. with
> no values skipped. (This is necessary, as the record is used to sort values,
> and the order can be changed by the customer)

Indeed you can't defer unique constraints. You can work around that
though, by adding say one million to each number and then changing it
to the right number. Using negative numbers works too.

Anyway, I think your problem could be solved in two steps:

update snark set custseq = custseq + 1000000;
update snark set custseq =
(select count(*) from snark s2
where s2.custid = snark.custid and s2.custseq <= snark.custseq);

Hope this helps,

> Here's sample code that demonstrates my question:
>
> create table snark (custid integer not null, custseq integer not null,
> unique(custid, custseq));
>
> insert into snark (custid, custseq) VALUES (1, 2);
> insert into snark (custid, custseq) VALUES (1, 4);
> insert into snark (custid, custseq) VALUES (1, 3);
> insert into snark (custid, custseq) VALUES (1, 1);

<snip>
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.