Hi all.
I've just spent the better part of a day trying to delete parent and child records related in a 1:M set. I've pored through the online Help and this forum and have learned that there is a sublety that isn't emphasized enough. I'd like to share what I've learned so that others can benefit. Here's the problem:
According to a note attached to the Help for "TBL".DELETE(), "For Alpha Five V3.02 and beyond, the record pointer will move to the next record after the "TBL".CHANGE_END() method is executed. For prior versions, the record pointer will remain on the deleted record."
That means that if you use a standard while/fetch_next() loop to delete multiple child records as mentioned in thread
http://msgboard.alphasoftware.com/alphaphorum/read.php3?sortby=lastreply&direction=desc&num=11&id=53033&thread=53016
and the online Help, you will actually delete *every other* child record. This happens because as soon as you delete a record, the record pointer automatically moves to the next record, so the fetch_next() will skip the one the pointer has just moved to. (I verified this by using Visual FoxPro 5.0 to view a table with records which had been deleted by a while/fetch_next() loop in A5. Since VFP 5.0 clearly shows deleted records in its browses, one of the few things I can't seem to do with A5, it was easy to see that "every other record" had been deleted.)
So how do you solve this problem? Again referring to the thread above, Tom Cone, Jr. suggested using "TBL".DELETE_RANGE(). That turns out to be an excellent suggestion as long as you construct the filter properly.
So, perhaps A5v6 should have an additional logical parameter attached to "TBL".DELETE() where .T. (default) moves the record pointer (current behavior) and .F. doesn't. That would provide the best of both worlds. For me, I'll just use "TBL".DELETE_RANGE() for now :). What do you think?
--Michael
I've just spent the better part of a day trying to delete parent and child records related in a 1:M set. I've pored through the online Help and this forum and have learned that there is a sublety that isn't emphasized enough. I'd like to share what I've learned so that others can benefit. Here's the problem:
According to a note attached to the Help for "TBL".DELETE(), "For Alpha Five V3.02 and beyond, the record pointer will move to the next record after the "TBL".CHANGE_END() method is executed. For prior versions, the record pointer will remain on the deleted record."
That means that if you use a standard while/fetch_next() loop to delete multiple child records as mentioned in thread
http://msgboard.alphasoftware.com/alphaphorum/read.php3?sortby=lastreply&direction=desc&num=11&id=53033&thread=53016
and the online Help, you will actually delete *every other* child record. This happens because as soon as you delete a record, the record pointer automatically moves to the next record, so the fetch_next() will skip the one the pointer has just moved to. (I verified this by using Visual FoxPro 5.0 to view a table with records which had been deleted by a while/fetch_next() loop in A5. Since VFP 5.0 clearly shows deleted records in its browses, one of the few things I can't seem to do with A5, it was easy to see that "every other record" had been deleted.)
So how do you solve this problem? Again referring to the thread above, Tom Cone, Jr. suggested using "TBL".DELETE_RANGE(). That turns out to be an excellent suggestion as long as you construct the filter properly.
So, perhaps A5v6 should have an additional logical parameter attached to "TBL".DELETE() where .T. (default) moves the record pointer (current behavior) and .F. doesn't. That would provide the best of both worlds. For me, I'll just use "TBL".DELETE_RANGE() for now :). What do you think?
--Michael
Comment