I'll expand on what I'm trying to accomplish.
I have a table called MASTER that contains names and addresses
I have a table that contains addresses that I received from a batch scrubbing
service that can provide me with new addresses at times and the same address
on file at times called SERVICES_CT_ADDRESS
I want to take the address in the SERVICES_CT_ADDRESS table and compare it
against the address in the MASTER table and if the address is different, I
want to update the address in the MASTER table with the address in the
SERVICES_CT_ADDRESS table. I know how to perform the update for the address,
but I just can't figure out how to be able to compare the two addresses
against each other so I can remove the ones that match.
The address in the MASTER table is stored in these fields: STREET1, STREET2,
CITY, STATE, ZIPCODE
The address in the SERVICES_CT_ADDRESS table is stored in these fields:
PRIMARYSTREETID, PREDIRECTION, STREETNAME, POSTDIRECTION, STREETSUFFIX,
UNITTYPE, UNITID, CITY, STATE, ZIPCODE This is why I have to perform the
CONCATENATE in order to combine the street address elements to match the
format of the STREET1 and STREET2 fields in the MASTER table.
This is an example of what the data looks like: (The STREET1 address is the
address from the SERVICES_CT_ADDRESS table, and the OLDSTREET1 address is the
address from the MASTER table. You'll notice the extra spaces in the
addresses listed under STREET1
601 MARTINIQUE AVE 2804 S LLEWELLYN AVE
1107 FULCHER LN 1107 FULCHER LN
1813 LINDEN ST 4736 N LOTUS AVE
735 735 FRANKLIN RD
Note, the last record listed "735 Franklin Rd" I manually updated the
STREET1 value to "735" just to see if the query below would return that
record, and it did not. It leads me to beleive that the issue is not with
the extra spaces in the STREET1 field. Do you have any suggestions?
SELECT STREET1, OLDSTREET1
WHERE STREET1 LIKE OLDSTREET1