Kjell Rilbe schrieb:

> isaac_cm wrote:
>
>> I want to exchange value of specific record and the one precede it , I
>> can do that using two update sql of course but I just wonder if it can
>> be done in one sql.
>
> There's no concept of "previous" or "next" in a DB table. There may be
> previous and next in an ordered cursor from a select for example, but
> that order is not preserved within the actual table data. That's why
> Alan asked what you mean.
>
> If I understand you correctly, you have two records (ignore what order
> they appear in your client application) and you want to swap values for
> one or more columns between the two records.
>
> This is not possible with a single update query. You have to select the
> first one, read the values, select the second one, read the values,
> update the first one with values from the second one, update the second
> one with values from the first one, commit transaction.
>
> With proper transaction handling, this will be atomic so that the
> updates won't appear to other transactions until you commit the
> transaction, and at that time, both records will be updated.
>
> Kjell

So you must know what your previous record is.

One solution is to use a field, that points to the next record of the
table (linked list).
The previous one is the one where the field is null.

Something in the trigger like that:

select id from mytable where id_next is null and id <> new.id into :id_prev

Then you have the id of the previous record and can do what you want
with it.
One statement must be:
update mytable set id_next = new.id where id = :id_prev;

--
Andreas