i tryed

UPDATE myTable m
SET m.field_b =
(
SELECT m2.field_a
from myTable m2
where m2.row_id = m.row_id + 1
) where m.row_id is not null

it writes null values ,
but if i try

UPDATE myTable m
SET m.field_b =
(
SELECT m2.field_a
from myTable m2
where m2.row_id = m.row_id
) where m.row_id is not null

it works .

row_id is integer pk
field_a and field_b are char 80

thanks





Fidel Viegas ha scritto:

>
> On Fri, Jun 13, 2008 at 12:03 PM, Svein Erling Tysvær
> <svein.erling.tysvaer@...
> <mailto:svein.erling.tysvaer%40kreftregisteret.no>> 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
> itself.
>
> 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.
>
> Fidel.
>
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG.
> Version: 7.5.524 / Virus Database: 270.3.0/1500 - Release Date: 12/06/2008 16.58
>