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.

Example Mine
Store Name
Address (might be mostly right)
City (Will be correct 99% of the time)
State
Fax
Phone
Lots of Network info fields
Inique Identifier <matches in both Databases>

Theres
Store Name
Address
City
State
Fax
Phone
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

use a multiple table update

examples of this are in the docs

If I am reading the update help correctly..

Make a second database on the same system and import the csv file

and the syntax would be similar to this??
update storesmine, storestheres
SET Address = Address,
city = city,
state = state,
zip = zip
WHERE uniqueid = uniqieid

yes, similar to that, but of course you have to qualify the column names, otherwise it's ambiguous which table they refer to

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

yes, like that

Ok I dont have something right...

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

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

English translation

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...

Logically this should work