Delete rows with exception join

[J.Pluta]

There are a couple of ways, but here's probably the easiest. Put the select in a subselect, and then do a DELETE from the original file where it matches a record in the EXCEPTION JOIN. To do that, you need to rename the files since you have two instances of the same file (call them A and B, or whatever). You then do a DELETE ... WHERE EXISTS on the subselect, adding a compare of the keys between the A file and the B file. I have two fields, you can have as many as you need. Note that I changed SELECT * to SELECT 1; on some query engines it makes no difference, but on some it can make a big difference to specify fewer fields, and it never hurts. Plus it makes it clear to me that I'm just looking for a record match, not checking any fields. From V5R4 on, you can also do a DELETE ... WHERE ... IN (see my second example). Prior to V5R4, when you have more than one key field you have to concatenate the keys into a single string on both the IN and the SELECT, because the IN syntax doesn't support multiple columns (this is true on a lot of DB engines). Joe

---

Code

[Guest.Visitor]

Delete rows with exception join

Joe Thank you for the answer to my question. I was able to get both methods to work but I had to make a slight change to the WHERE EXISTS SUBSELECT statement. I changed "where swpgm <> 'COM403R'" to "where b.swpgm <> 'COM403R'". Without the "b." the delete command wanted to remove 128 rows but I already knew that the correct number of records that needed to be removed from the table was 133. Adding the "b." allowed the correct number of records to be removed from the file. Another interesting point to note. The WHERE EXISTS SUBSELECT command completed the delete operation in nine seconds. The WHERE IN SUBSELECT took 236 seconds to complete. That is a significant order of magnitude difference between the two SQL statements. Here is the information related to the two files in case anyone could explain why there was such a large difference between the two methods. DOMSW100 is a keyed physical file with 15,989 records. MBCDREP is a non-keyed physical file with 16,706 records. Neither file had any logicals. Both SQL statements removed 133 records from DOMSW100. We are running V5R4 and reasonably up to date on CUM and Hipers. Steven Lukas

[Guest.Visitor]

Delete rows with exception join

This is probably an easy question to answer to anyone who uses SQL on a regular basis. We have a table with extraneous records that need to be removed due to a programming bug. I can obtain the row set that I want to delete using the following: select * from domsw100 exception join mbcdrep on (swaenb = cdaenb and swdccd = cddccd and swcvnb = cdcvnb and swfcnb = cdfcnb) where swpgm <> 'COM403R' How do I delete this selected set of records?

[J.Pluta]

Delete rows with exception join

Sorry about that, Steven. It is crucial that you qualify your comparisons in the WHERE EXISTS. As to the time discrepancy, I can only think that WHERE IN first builds a temporary table, and then in effect executes a WHERE EXISTS on that temporary table. Joe