|
Ok so what about using a constraint to enforce you
uniqueness and then either inside a transaction or after locking the table drop
the constraint, do the update and then re-add the constraint. I am not sure how
fast this would be since I guess that adding the constraint may take some
time! Your going to have some issues with time since the index will need
updating for every key and this will not be O(0). Another other option is
to use a generated id as the primary key and then you won't need your unique
index on the composite primary key.
Matthew
----- Original Message -----
Sent: Monday, November 24, 2003 3:12
PM
Subject: Re: [SQL] increment int value in
subset of rows?
On Mon, 24 Nov 2003 09:21:39 -0000
"Matthew Lunnon" <[EMAIL PROTECTED]> threw this
fish to the penguins:
> You could write a function to do it.
>
> Matthew
That would save me the external interaction, but still
amount to ~1000
sql queries -- I'm hoping to find something O(0), i.e. a
few queries
regardless of the number of rows...
>
----- Original Message -----
> From: george young
> To: [EMAIL PROTECTED]
> Sent: Monday, November 24, 2003 1:59
AM
> Subject: [SQL] increment int value in subset of
rows?
>
>
> [postgresql 7.4, SuSE x86
linux]
> I have a table "rtest" with primary key (run,seq)
and other data. For a given value
> of "run", seq is a
sequential run of integers, 1,2,3,4.. Now I want to
>
insert a row into this "sequence", say run='foo', seq=2, adjusting the seq up
for
> all subsequent foo rows. My first
thought
> was just:
>
update rtest set seq=seq+1 where run='foo' and seq>1;
>
which gets:
> ERROR: Cannot insert a
duplicate key into unique index rtest_pkey
> no surprise
:-(.
>
> This doesn't work, since the *order* of
execution of these updates
> is not guaranteed, and I
actually would need to start with the highest
> value of
seq and work down. There may be a thousand or so rows for
'foo'
> run, so an external loop of queries would be very
expensive.
> How can I increment all the seq values
for foo columns where seq > something?
>
> create
table rtest(run text,seq int,data int,primary key
(run,seq));
> insert into rtest
values('foo',1,11);
> insert into rtest
values('foo',2,22);
> insert into rtest
values('foo',3,33);
> insert into rtest
values('foo',4,44);
> insert into rtest
values('bar',1,99);
>
> I want to shift all foo rows
and insert a new one so that:
> select *
from rtest where run='foo' order by seq;
> would
get:
>
> run | seq | data
>
-----+-----+------
> foo | 1 |
11
> foo | 2 |
999
> foo | 3 |
22
> foo | 4 |
33
> foo | 5 | 44
--
I cannot think why the whole bed of the ocean is
not one
solid mass of oysters, so prolific they seem. Ah,
I am wandering!
Strange how the brain controls the brain!
-- Sherlock Holmes in "The Dying
Detective"
---------------------------(end of
broadcast)---------------------------
TIP 1: subscribe and unsubscribe
commands go to [EMAIL PROTECTED]
_____________________________________________________________________
This
e-mail has been scanned for viruses by MCI's Internet Managed Scanning
Services - powered by MessageLabs. For further information visit http://www.mci.com
|
