Author Topic: Import to wrong address (Read 815 times)

markimus · November 03, 2011, 01:36:15 pm

I ran an import of 10,000 +- contacts and discovered after that I mapped to the wrong address. I wanted to map to street address but I mapped to additional address. When I try to run a script [UPDATE civicrm_address SET supplemental_address_1 = street_address;] I am not sure if that will actually do the trick.
The street address table in the database says the following.
street_address Concatenation of all routable street address components (prefix, street number, street name, suffix, unit number OR P.O. Box). Apps should be able to determine physical location with this data (for mapping, mail delivery, etc.).

Is there a way to address this? IF I want to pull from supplemental_address_1 = where am I putting it?

Hershel · November 04, 2011, 02:42:16 am

Hello there markimus.

The code:

Code: [Select]

UPDATE civicrm_address SET supplemental_address_1 = street_address;
looks correct to me. What you can do to confirm is find a contact in your browser, see his Contact ID in the URL and then find his address record in the civicrm_address table (by searching on the contact_id field).

Then you could see if his "23 Oak St." is indeed in supplemental_address_1, and his street_address is empty. If so, your SQL is correct. If not, then let us know what you do see.

markimus · November 04, 2011, 05:55:26 pm

I checked and it seems that the fields are lines up as you say. But when I run the script the fields end up empty. In other words the data does not move from supplemental to street

Hershel · November 05, 2011, 10:57:25 am

I don't quite follow. Let's say you're looking at record 99 in civicrm_address. To view it you use:

Code: [Select]

SELECT * FROM civicrm_address WHERE id=99;
right? You then see all the fields. Now if you run this, which is to update just that one record:

Code: [Select]

UPDATE civicrm_address SET supplemental_address_1 = street_address WHERE id=99;
Does it give an error? If not, then if you run the SELECT above, is it still the same? If so, can you post this full set of 3 queries and the result each time?

markimus · November 05, 2011, 11:24:20 am

So I ran it on this contact

Step one
Street Address Supplemental
2 1 5 1 0 NULL NULL NULL NULL NULL NULL NULL NULL 46 7155 189st

Step 2 Run the UPDATE civicrm_address SET supplemental_address_1 = street_address WHERE id=2;
and got this msg = 1 row affected. ( Query took 0.0101 sec ) no errors

Step 3 review the contact info

NULL NULL NULL NULL NULL NULL NULL NULL NULL

Hershel · November 05, 2011, 01:09:18 pm

I examined this site offline myself and it seems the SQL required is:

Code: [Select]

UPDATE civicrm_address SET street_address = supplemental_address_1 WHERE street_address IS NULL;
HTH

CiviCRM Community Forums (archive)

News:

Author Topic: Import to wrong address (Read 815 times)

markimus

Import to wrong address

Hershel

Re: Import to wrong address

markimus

Re: Import to wrong address

Hershel

Re: Import to wrong address

markimus

Re: Import to wrong address

Hershel

Re: Import to wrong address