Dan, This is a classic recursive/cascading delete problem. Your DELETE statement only removes one "generation" of orphans per execution — and here's why. On the first run, it deletes rows that have no parent in tmpbompi. But once those rows are gone, their children — which did have a valid parent before — are now orphaned themselves. Each run only peels back one layer, which is why you have to keep repeating it manually. The fix is a WHILE loop that keeps running the DELETE until no more orphan rows remain:
SET VAR vDelCount INTEGER = 1 WHILE .vDelCount > 0 THEN DROP TABLE tmpbompi PROJECT INMEMORY tmpbompi FROM tmpbomtree USING tid tparentid DELETE ROWS FROM tmpbomtree + WHERE pvpntitle NOT LIKE '00-%' + AND tparentid NOT IN (SELECT tid FROM tmpbompi) SELECT (COUNT(tid)) INTO vDelCount INDIC iv1 FROM tmpbomtree + WHERE pvpntitle NOT LIKE '00-%' + AND tparentid NOT IN (SELECT tid FROM tmpbompi) ENDWHILE CLEAR VARIABLES iv%,vDelCount RETURN A few important points about this approach: Rebuild tmpbompi inside the loop. Your original code takes a snapshot of tmpbomtree into tmpbompi before any deletes happen. That snapshot never changes, so after the first DELETE pass, tmpbompi still contains tid values for rows that no longer exist in tmpbomtree. By dropping and re-projecting tmpbompi at the top of each loop iteration, the IN-list always reflects the current live state of the table. How it converges, pass by pass: Pass What gets deleted 1 Rows with no parent at all (true root orphans) 2 Rows whose parent was deleted in pass 1 3 Next generation up N Loop exits when COUNT returns 0 The loop will run exactly as many passes as the tree is deep — for a typical BOM structure, this is usually under 10 iterations. When the DELETE removes nothing, vDelCount comes back as 0, and the WHILE exits cleanly. What remains when the loop finishes is only the 00-% top-level rows and any children that have a complete, unbroken ancestor chain leading back to one of them — exactly what you want. One note on the SELECT ... AS vDelCount syntax: confirm your R:BASE build supports direct variable assignment from a query that way. If not, use: SET VAR vDelCount = (SELECT COUNT(tid) FROM tmpbomtree + WHERE pvpntitle NOT LIKE '00-%' + AND tparentid NOT IN (SELECT tid FROM tmpbompi)) Either way, the loop needs a reliable row count after each DELETE pass to know when to stop. There you have it! Note: TRACE the code for any corrections, and correct it as you see fit before you put this into production. -- Razzak www.rbase.com https://www.rbase.com > On 05/27/2026 5:23 PM EDT Dan Goldberg <[email protected]> wrote: > > I create a temporary table that I want to delete all the orphan childs that > do not have parents except the ’00-%’ top levels > > --create table > > CREATE INMEMORY TABLE tmpbomtree (tid INTEGER, tparentid INTEGER, pvpntitle + > > TEXT 75, tpvimage INTEGER) > > --insert all rows > > INSERT + > > INTO tmpbomtree (tid, tparentid, tpvimage) SELECT pvplpartid, pvpllistid, 1 + > > FROM pvpl WHERE pvplpartid IN (SELECT PVPNID FROM pvpn + > > WHERE modyear = .vmodyear AND pvpntype = 'pl') > > --update title > > UPDATE tmpbomtree SET pvpntitle = (t2.pvpnpartnumber + '-' + t2.pvpntitle) + > > FROM tmpbomtree t1, pvpn t2 WHERE t1.tid = t2.pvpnid > > --remove top levels that do not start with 00 > > --create temp table to look at to delete(which is a copy of tmpbomtree) > > PROJECT INMEMORY tmpbompi FROM tmpbomtree USING tid tparentid > > DELETE ROWS FROM tmpbomtree + > > WHERE pvpntitle NOT LIKE '00-%' AND tparentid NOT IN (SELECT tid FROM > tmpbompi) > > When I run this it only deletes a portion of the rows. I have to run the > delete command many times until they are all gone. > > Any Ideas or tips? > > Dan Goldberg > > -- > For group guidelines, visit > http://www.rbase.com/support/usersgroup_guidelines.php > --- > You received this message because you are subscribed to the Google Groups > "RBASE-L" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected] > mailto:[email protected]. > To view this discussion visit > https://groups.google.com/d/msgid/rbase-l/PH3PPF5FDE66ECBC110752666339EBE3E9BD4082%40PH3PPF5FDE66ECB.namprd10.prod.outlook.com > > https://groups.google.com/d/msgid/rbase-l/PH3PPF5FDE66ECBC110752666339EBE3E9BD4082%40PH3PPF5FDE66ECB.namprd10.prod.outlook.com?utm_medium=email&utm_source=footer. > -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion visit https://groups.google.com/d/msgid/rbase-l/685727965.312529.1779929231478%40email.ionos.com.
