RE: delete orphaned records in SQL

While this works, a "potentially" faster method is the EXISTS clause.

delete from DETAILFILE A
where exists
        (select *
        from HEADERFILE B
        where A.DKEY1 = B.HKEY1 and
                A.DKEY2 = B.HKEY2)

SQL can do all the necessary processing at the access path, without needing
to read the HEADERFILE record at all.  Of course, if access paths don't
exist, SQL will have to either create them or use a different method
internally to build the result. Bye bye performance. In this case, you'd
need an index of DKEY1, DKEY2 for DETAILFILE and HKEY1, HKEY2 for
HEADERFILE.

Eric DeLong
Sally Beauty Company
MIS-Sr. Programmer/Analyst
940-898-7863 or ext. 1863



-----Original Message-----
From: Fisher, Don [mailto:Dfisher@roomstoreeast.com]
Sent: Friday, March 08, 2002 7:18 AM
To: 'midrange-l@midrange.com'
Subject: RE: delete orphaned records in SQL


I can't speak to more recent releases, but at V4R1 you must concatenate the
keys.  In your case the statement would be:
Delete from DETAILFILE where DKEY1 || DKEY2 not in (Select HKEY1 || HKEY2
from HEADERFILE)

Any numeric key fields should be converted to character using the DIGITS
function.

Hope that helps.

Donald R. Fisher, III
Project Manager
The Roomstore Furniture Company
(804) 784-7600 ext. 2124
DFisher@roomstoreeast.com

<clip>
I want to write an sql statement to delete orphaned records from a detail
file (as relates to the header).
<clip>
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.