SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Help copying data from one mysql table to another plz!

    I'm tyring to set up a script which will copy data from one MYSQL table to another. Both tables are within the same database.
    The first table has around 30 columns in it whereas the second table only has 3.
    Both tables will have an Id column which will hold a unique value for each row.
    I need to create a script which will get all of the data from the first database based on a WHERE clause.
    The script then needs to check the second database to see if a row with the unique id already exists in the second table if it does already exist I need the script to disregard that row of data.
    If it doesn't already exist in the second table I need to then insert this new row into the table.

    Can anyone help me out with the above please? Are there any scripts / tutorials that I can look at that anyone already knows about?

    Thanks for the help in advance.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    1) Put an unique index on the id column of the second table (that id column must not be autoincremental)
    2) Use INSERT IGNORE ... SELECT ... to copy the rows from the first to the second table
    Code:
    INSERT IGNORE
    INTO table2
    SELECT 
        id
      , column1
      , column2
    FROM table1
    WHERE ....
    The IGNORE means the INSERT will discard any rows that cause a duplicate key error. So putting a unique index on the id column makes sure you won't insert rows with an id that already exists in table2.

  3. #3
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Fantastic!! Thanks for the quick reply Guido. I'll give it a go and let you know how I get on.

  4. #4
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm trying to set up the script using INSERT IGNORE but it's not working. I keep getting a column count doesn't match error.
    Here's the code I have on my php page. Can you tell me where I'm going wrong please?

    PHP Code:
    $query "INSERT IGNORE Used_Notes SELECT Feed_Id, Full_Registration FROM Used_Stock WHERE Feed_Id='11102' AND Model LIKE '%M3%' OR Feed_Id='11102' AND Model LIKE '%M5%' OR Feed_Id='11102' AND Model LIKE '%M6%' OR Feed_Id='11102' AND Model LIKE '%Z4 M%' OR Feed_Id='11102' AND Model LIKE '%X5 M%' OR Feed_Id='11102' AND Model LIKE '%X6 M%'" ;
    $result mysql_query($query) or die(mysql_error()); 

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You said table 2 has 3 columns, but in your INSERT you only have two.

  6. #6
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Table 2 does have 3 columns but I only take two of the columns form table 1 to populate two of the columns. The other one is done by manual input.
    Is there a way I can change the script to accomodate this?

    Thanks for your help.

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by sketchgal View Post
    The other one is done by manual input.
    Manual?
    If this means that column gets its value later, just put '', 0, NULL or whatever value the column should get until that manual input, in the right place in the SELECT.
    For example, if the "manual" column is the second column of the table, change the query to:
    Code:
    INSERT IGNORE Used_Notes 
    SELECT 
        Feed_Id
      , NULL
      , Full_Registration 
    FROM Used_Stock 
    WHERE 
      ...
    I used NULL here, just change it into whatever value you want the column to have.


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
  •