find on one related record but replace field in another
I have two MySQL tables with a filemaker front end, governments and government characteristics (there are a couple of other tables that are not relevant for this issue). Governments consists of two fields (censusID and name), government characteristics has three (censusID, characteristicID, CharData). One of the things I am tracking is the address of the government. An address is stored as 4 records in the characteristics database, street address, city, state, and zip code, each of which have a different characteristicID code. There are two types of addresses, mailing and physica so there are a total of 8 address characteristic IDs. I am trying to find governments whose census records show a physical address, but are really a mailing address. This is discovered by searching for variants of the PO Box string in CharData. Then I want to flip over the relevant characteristicIDs to their mailing address equivalents. I have a filemaker script that can do it. It is very slow, about 9 seconds per record. It also semi-regularly crashes Filemaker 12v4 just like it did v3.
I would like to do an alternate where I can somehow get the right found sets (separately zip code, state, city, and street address of governments whose street addresses are PO Boxes) and do a replace field contents. I believe that this will take less time to run through. Am I right on the speed and what's the proper way to do such a search?