Copy some table records into the same table make some problems

Question

DEFINE BUFFER bfSource FOR tableA.
DEFINE BUFFER bfTarget FOR tableA.

DO FOR bfTarget TRANSACTION:
    FOR EACH bfSource  WHERE  bfSource.user_id = 0 NO-LOCK:
        CREATE bfTarget.
        BUFFER-COPY bfSource  EXCEPT id TO bfTarget.
        ASSIGN 
            bfTarget.user_id = 1.
    END.
END.

When i use this query the last iteration can randomly couse some problems. The AVM try to find the next record, but their is none, sometimes the AVM finds the new created record (before the user_id is <> 0). I have some ideas to prevent this , horribil to find, bug, but i dont know what is best practise. I also tryed to change the position of the transaction scope with no effect.

Posting the errors you encounter would be useful for figuring out what's gone wrong. And in general, for questions about tables (db or temp-) showing the schema - fields and indexes - helps a lot too. — nwahmaet, Feb 01 '21 at 14:29
It is only an example, i get no error, even with only the id field and without buffer-copy the problem can occur. In the last iteration i copy the previously created record becous the avm finds it before the id is <> 0. — JulesVerne, Feb 05 '21 at 07:57

score 0 · Answer 1 · edited Mar 13 '21 at 19:41

0

My solution , at the moment, is:

DEFINE BUFFER bfSource FOR tableA.
DEFINE BUFFER bfTarget FOR tableA.

REPEAT PRESELECT EACH bfSource WHERE bfSource.user_id = 0 NO-LOCK:
    FIND NEXT bfSource.
    DO FOR bfTarget TRANSACTION:
        CREATE bfTarget.
        BUFFER-COPY bfSource  EXCEPT id TO bfTarget.
        ASSIGN 
            bfTarget.user_id = 1.
    END.
END.

edited Mar 13 '21 at 19:41

marc_s

732,580
175
1,330
1,459

answered Feb 01 '21 at 05:15

JulesVerne

43
8

You can have the `ASSIGN` be part of the `BUFFER-COPY` statement (the `.` after `bfTarget` makes it a separate statement. – nwahmaet Feb 01 '21 at 14:27

Copy some table records into the same table make some problems

1 Answers1