> Uhm, please ignore this email, it looks like this can't be done using
> transactions
>
> begin transaction;
> update book set "order"=2 where id=1;
> update book set "order"=1 where id=2;
> commit;
>
> ERROR: duplicate key violates unique constraint "book_order_key"
Alternatively, if you want to do it in a transaction, and you
have a uniqueness constraint on the "order" field, you can do
something like
BEGIN TRANSACTION
UPDATE book SET order=(
SELECT Max(order)+1
FROM book
)
WHERE id=1
UPDATE book SET order=1 WHERE id=2
UPDATE book SET order=1 WHERE id=1
COMMIT;
as the problem seems to be stemming from duplicating "order"
which is defined as unique, if even only for a fraction of the
transaction.
If "order" is allowed to be Null, and you can assert that there
are no Nulls in the table for this field, you can simplify the
above to
BEGIN TRANSACTION
UPDATE book SET order=NULL WHERE id=1
UPDATE book SET order=1 WHERE id=2
UPDATE book SET order=2 WHERE id=1
COMMIT;
Just a few more ideas,
-tkc
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
