Subject Re: [firebird-support] Re: update previous and current record in
Author Andreas Hesse
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 <> 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 = where id = :id_prev;