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)
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);
begin transaction;
DELETE FROM snark WHERE custid=1 AND custseq=2;
UPDATE snark SET custseq=custseq-1 WHERE custid=1 and custseq>2;
This generates an error!
ERROR: duplicate key violates unique constraint "snark_custid_key"
I've tried putting an "order by" clause on the query:
UPDATE snark SET custseq=custseq-1 WHERE custid=1 and custseq>2
ORDER BY custseq ASC;
But that got me nowhere. Also, I can't defer the enforcement of the
constraint, as, according to the manual, this only works for foreign keys.
Any ideas where to go from here?
-Ben
--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978
