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
Code
Comment