From: | Stuart McGraw <smcg2297(at)frii(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | changing multiple pk's in one update |
Date: | 2009-04-08 02:47:16 |
Message-ID: | 49DC1034.1060705@frii.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hello all,
I have a table with a primary key column
that contains sequential numbers.
Sometimes I need to shift them all up or down
by a fixed amount. For example, if I have
four rows with primary keys, 2, 3, 4, 5, I
might want to shift them down by 1 by doing:
UPDATE mytable SET id=id-1
(where "id" is the pk column) so that the pk's
are now 1, 2, 3, 4.
When I try to shift them up by using +1 in the
above update statement, I get (not surprisingly)
a duplicate key error. I also realize that the
-1 case above works only by luck.
So my question:
Is there some way, perhaps with ORDER BY, that
I can achieve the change I want with a single
update statement? (If I have an unused key
range large enough, I suppose I could update
all the keys to that range, and then back to
my target range but the requires two updates
(there are a lot of foreign keys referencing
these primary keys) and requires that I have
an available range, so a single update statement
would be preferable.)
Thanks for any enlightenment.
From | Date | Subject | |
---|---|---|---|
Next Message | Mario Splivalo | 2009-04-08 17:08:55 | ENUM vs DOMAIN vs FKyed loookup table |
Previous Message | Glenn Maynard | 2009-04-08 02:34:38 | Nested selects |