Subject Re: [firebird-support] read the next record of the table
Author Fidel Viegas
On Fri, Jun 13, 2008 at 12:03 PM, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:
> What you do should work. The one thing I would expect to cause this to fail,
> is if you didn't commit the transaction. Another things that could be of
> influence, is the field type of row_id, if it is an integer, decimal or date
> (provided dialect 3) field, I cannot see how this could fail. Floating point
> numbers is something completely different, they're approximate (may be
> 1.00000000232 when you think it is just 1) and shouldn't be used with
> comparison for equality. Of course, if field_a and field_b is incompatible
> field types, then you would also get trouble, but that would normally be
> very visible for you when you try to prepare the statement.

Hi Set,

From what I can see in the statement, that will update every single
row, because there is no filter condition in the update statement

if you do "update table set col = xxx", this will change every single
row in the table. The subselect is correct, but that is a condition
missing in the udpate statement, which I haven't figured out what it
is. Here is how it should be:

UPDATE myTable m
SET m.field_b =
SELECT m2.field_a
from myTable m2
where m2.row_id = m.row_id + 1
) where <condition>;

I haven't really figured what the <condition> is, but would like to
know if someone comes up with the solution to this.