Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    6

    Unanswered: Mysql update + insert question

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a multiple table update

    examples of this are in the docs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2005
    Posts
    6
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, similar to that, but of course you have to qualify the column names, otherwise it's ambiguous which table they refer to
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2005
    Posts
    6
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, like that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2005
    Posts
    6
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •