Re: [BUGS] updating unique columns

#1

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Quote:

UPDATE
orderby SET orderby = orderby +10
WHERE groupid = 1;
-> FAILS because of UNIQUE INDEX

One workaround is to do it in two steps, assuming that
orderby is > 0 for all rows you are changing:

BEGIN;
UPDATE mytable SET orderby = -orderby WHERE groupid = 1;
UPDATE mytable SET orderby = -orderby+10 WHERE groupid = 1;
COMMIT;

Quote:

UPDATE
orderby SET orderby = orderby +10
WHERE groupid IN
( SELECT groupid
FROM TABLE WHERE group_id = 1
ORDER BY orderby DESC )

No idea what you are trying to do here: try posting the actual SQL
used. However, an ORDER BY has no meaning inside of a subselect
passed to IN, as IN treats the list as bag of values, and does not
care about the internal order.

- --
Greg Sabino Mullane greg (AT) turnstep (DOT) com
PGP Key: 0x14964AC8 200606011030
http://biglumber.com/x/web?pk=2529DF...9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFEfvpFvJuQZxSWSsgRAjQlAKDTNIpwbSEk0gcQp2pI7L okG+qwWgCgt/b6
/7ZWYDb4gufE4b0zCHyFZgg=
=4LQ8
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#2

""Greg Sabino Mullane"" <greg (AT) turnstep (DOT) com>

Quote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

| UPDATE
| orderby SET orderby = orderby +10
| WHERE groupid = 1;
| -> FAILS because of UNIQUE INDEX

One workaround is to do it in two steps, assuming that
orderby is > 0 for all rows you are changing:

BEGIN;
UPDATE mytable SET orderby = -orderby WHERE groupid = 1;
UPDATE mytable SET orderby = -orderby+10 WHERE groupid = 1;
COMMIT;

Should we invent something to defer the check of uniqueness
to the end of the SQL command?

Regards,
William ZHANG

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] updating unique columns

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] updating unique columns - 06-01-2006 , 09:34 AM

Re: [BUGS] updating unique columns - 06-02-2006 , 05:33 AM