It's a low-tech solution but you could:
begin update t1 set seq=-1 where id=5 update t1 set seq=5 where id=4 update t1 set seq=4 where id=-1 end
This is assuming that you don't naturally have -1 as a valid value of
that column.
chester c young wrote:
>table t1:
> id integer primary key,
> seq integer not null unique
>
>the seq is for ordering the rows as the user likes. however, if the
>rows are moved around, eg
> begin
> update t1 set seq=4 where id=5
> update t1 set seq=5 where id=4
> end
>will bomb because the first update has two rows of seq=4 (although
>correct after the transaction).
>
>I thought "deferrable initally deferred" would fix this, but the phrase
>is not allowed on unique.
>
>is this correct? any ideas?
>
>
>__________________________________________________
>Do You Yahoo!?
>Tired of spam? Yahoo! Mail has the best spam protection around
>http://mail.yahoo.com
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster
>
>