Eliminating duplicates based on blank field

[djdonny]

I have a database with several fields and over 20000 records. However, to simplify the problem, suppose I had three fields, A, B and C:

A | B | C |
1 X Y Z
2 X Y
3 X P
...

I want to remove records like #2 that are identical to another record, #1, in fields A and B, but is blank in field C, where #1 is not. If I index the file A->B->C, #1 always gets deleted to KeyViol instead of #2. Is it possible to do this, preferably using a query or other non-ObjectPal procedure? (I've never used ObjectPal, but if that's the only option, please post it and I'll learn it.)

 

[NotAllThere]

Your requirement could be executed a lot slicker with OPal, but if you want a query then try process below. You may want to first try with a smaller selection of records to prove the result is what you want.

1. Backup your table.
2. Also make a copy of the table to TBL_COPY.DB in the same directory.
3. Create a query with the original and TBL_COPY.DB tables.
4. Join field 1 on both tables. (A in your example). Join field 2 on both tables.
5. In field 3 of the original table (C in your sample) enter "BLANK" (without quotes).
6. Under the table name for the original table (leftmost column of query) right-click and select Delete.
7. Run the query and see if this is what you want.

With 20k records in each table, I suspect this will be tad slow...

Regards

 

[djdonny]

Thank you. I didn't think of creating a copy of the original table and then comparing the two. (I think I may need to put "not blank" in the copied table's C field for it to do what I want, but I'll play around with it.)