SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Nov 2001
    Location
    Fort Lauderdale
    Posts
    814
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL: Copy specific fields from Table A into Table B

    MySQL 5
    -----

    Okay basically, I have 2 tables which have over 2,000 records:

    1. I have Table A which has the following fields:
    address, zip, state, country, ID(this is a primary Key)

    2. I have Table B which has the following fields:
    address, zip, state, country, ID(this is a primary Key), + 10 other fields.

    3. I need to import all the fields from Table A into Table B. All the 10 fields in Table B are populated so I can not lose this data. The ID is the only thing that matches both tables.

    4. The field structure from both tables are the same, so if needed Table A can overwrite all the data in Table B only pertaining to the fields being copied over.

    I am trying to do this via phpmyadmin using an SQL statement. I tried to export Table A and import it into Table B but it is not working and I get timeout errors.

    I've googled for over an hour but even though I find similar questions.. I can not find my exact question and to be honest I am pretty lost.

    Any help would be much appreciated.

  2. #2
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try
    Code:
     
    update tblB
    set tblB.colA = (select tblA.colA from tblA where tblA.ID = tblB.ID)
    you will need a set...... for each column you want to update.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    UPDATE tableA
    INNER
      JOIN tableB
        ON tableB.id = tableA.id
       SET tableB.address = tableA.address
         , tableB.zip     = tableA.zip
         , tableB.state   = tableA.state
         , tableB.country = tableA.country
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Nov 2001
    Location
    Fort Lauderdale
    Posts
    814
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you both for your replies, I used the code below and it worked perfect! Thank you!

    Quote Originally Posted by r937 View Post
    Code:
    UPDATE tableA
    INNER
      JOIN tableB
        ON tableB.id = tableA.id
       SET tableB.address = tableA.address
         , tableB.zip     = tableA.zip
         , tableB.state   = tableA.state
         , tableB.country = tableA.country


Bookmarks

Posting Permissions

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