Ok I am a newbie.. I will admit it... I have a issue that I am trying to wrap my head around on how the best way it to do this
Situation. Two Databases. One I control the other I can just get a CSV extract from both contain close to the same information. Mine contains Network information and "best guess" on the location address. the other contains VERIFIED address information fax number and phone number. mine are best guesses and compiled over several years.
My Database contains several tables. The Primary Key is called store_name this key must be unique since several other applications right now key off of this field
Both Databases now contain a matching unique identifier in one field so there is a relationship built between the information.
There has to be a better way of updating the 14 other fields that I want "corrected" information off of other than manually typing them in.
Address (might be mostly right)
City (Will be correct 99% of the time)
Lots of Network info fields
Inique Identifier <matches in both Databases>
Hours of operation
Unique Identifier <matches in both Databases>
I want there Address City State Fax Phone and Hours of operation to overwrite my Address City State Fax and Phone number.
How can I do this? this is several thousand records and I really do not want to do all this manually
This is using mysql 4.0 on a Solaris base.. so Scripts are a option
**Note.. I can at this time only get a CSV extract of there information
I am trying to get the syntax down but shouldnt something like this work
insert into stores_new (Street_Address,City,) where stores_new.Infomanager_location_code=infomanager_s tores.Infomanager_location_code;
Stores_new = my Database
Infomanager_location_code = unique identifier
infomanager_stores = database created from csv file
On the previous example I am not quite understanding what I have to qualify.. are you refering to ??
update stores_new, infomanager_stores
SET stores.new.Address = infomanager_stores.Address,
stores_new.city = infomanager_stores.city,
stores_new.state = infomanager_stores.state,
stores_new.zip = infomanager_stores.zip
WHERE stores_new.uniqueid = infomanager_stores.uniqieid
In stores_test I have removed All Street_address information.. so I should get returns
I get a return of
mysql> update stores_test, infomanager_stores set stores_test.Street_address = infomanager_stores.Street_Address where infomanager_stores.Infomanager_location_code = stores_test.Infomanager_location_code;
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 2124468
Current database: stores_test
update stores test from infomanager stores. Set the stores test street address to infomanager stores street address when the infomanager stores infomanager location code is equal to the stores test infomanager location code and execute...