Subject Re: [firebird-support] Re: update previous and current record in one update statement
Author Kjell Rilbe
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 Rilbe
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64