Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2012
    Location
    New York
    Posts
    5

    Unanswered: Merging column data from multiple databases

    Hey!

    I have two SQL databases. One is an older backup of the other.

    I would like to merge only 1 specific table from [user_database0] into [user_database1] using either ssh or inside phpMyAdmin.

    That table that I want to restore from backup is called [prefix_table].

    However, I don't want to restore all columns from that table [prefix_table], just the [comment] column.

    One of my biggest concerns is that some of the rows from the [prefix_table] have been deleted and I DO NOT want to restore those deleted rows from the old database.


    Here is an Example:

    - Merge table [prefix_table]from [user_database0]:

    prefix_table
    +------------------------------------------------------+
    | id | name | comment | age | person_id |
    ++++++++++++++++++++++++++++++++
    | 1111 | name1 | old text 1 | 01 | 001 |
    +------------+-------+------------+-----+-----------+
    | 2222 | name2 | old text 2 | 02 | 002 |
    +------------+-------+------------+-----+-----------+
    | 3333 | name3 | old text 3 | 03 | 003 |
    +------------+-------+------------+-----+-----------+
    | 4444 | name4 | old text 4 | 04 | 004 |
    +------------+-------+------------+-----+-----------+



    -Into table [prefix_table] in [user_database1] :

    prefix_table
    +----------------------------------------------------------+
    | id | name | comment | age | person_id |
    ++++++++++++++++++++++++++++++++++
    | 1111 | namenew | new text 1 | 99 | 001 |
    +------------+---------+------------+-----+-------------+
    | 4444 | name4 | new text 4 | 04 | 004 |
    +------------+---------+------------+-----+-------------+
    | 5555 | name5 | text 1 | 05 | 005 |
    +------------+---------+------------+-----+-------------+
    | 6666 | name6 | text 2 | 06 | 006 |
    +------------+---------+------------+-----+-------------+


    - Resulting database [user_database1]:

    prefix_table
    +-----------------------------------------------------------+
    | id | name | comment | age | person_id |
    +++++++++++++++++++++++++++++++++++
    | 1111 | namenew | old text 1 | 99 | 001 |
    +------------+---------+------------+-----+--------------+
    | 4444 | name4 | old text 4 | 04 | 004 |
    +------------+---------+------------+-----+--------------+
    | 5555 | name5 | text 1 | 05 | 005 |
    +------------+---------+------------+-----+--------------+
    | 6666 | name6 | text 2 | 06 | 006 |
    +------------+---------+------------+-----+--------------+

    So it basically has to check if the table [prefix_table] matches in both databases then overwrite the data in the [comment] column. Note that if other column data changes, it should leave it as is, only the [comment] should be updated.

    In Summary (both databases are on same server in one phpMyAdmin account)

    FROM: [user_database0].[prefix_table].[content]

    TO: [user_database1].[prefix_table].[content]

    IF: [id] column matches in both tables.


    Greatly appreciate any advice.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    providing that the databases are ont he same physical server you can join tables in different databases is you fully qualify the table name with a databasename as well.
    http://dev.mysql.com/doc/refman/5.0/...ualifiers.html

    so you can perfomr what you want using a join and or subselect
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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