Results 1 to 9 of 9
  1. #1
    Grandma broke her coccyx! Uncle Rico's Avatar
    Join Date
    May 8th, 2007
    Location
    North Carolina
    Posts
    2,238
    Update Mysql Table Using Inner Join Problem
    I am working on a php script that will update a mysql table using another mysql table. TableA and TableB have the same filed names. Each tables has around 150,000 records. TableA is my main table that has my live data. TableB has some potential updates like price for one example that I want to update in TableA.

    TableA has
    ID PROGRAM PRICE ....

    TableB has
    ID PROGRAM PRICE ....

    My query:
    "UPDATE TableA A INNER JOIN TableB B ON A.ID = B.ID AND A.PROGRAM = B.PROGRAM SET A.PRICE = B.PRICE";

    Now this is a valid mysql query, but after running for a minute, I see the following message:

    Unable to query: Server shutdown in progress

    I was thinking that using an update with a join would be the way to update the quickest, but it looks like I may have gone over some memory limit, although were only talking about 150K records.

  2. #2
    ABW Ambassador Greg Rice's Avatar
    Join Date
    January 18th, 2005
    Location
    Ohio
    Posts
    4,889
    I'm no database expert but here is how I update one table with another:

    UPDATE TableA,TableB SET TableA.PRICE=TableB.PRICE
    WHERE TableA.ID=TableB.ID;

    Of course this only updates one field at a time so I'm not sure if this can be used for multiple fields at the same time.
    Greg Rice Affiliate Program Management
    www.gocmc.com info(AT)gocmc.com | 330-259-1223

    Join us! - MiNeeds.com | DiscountCandleShop/CheeseSupply | Feng Shui Plaza

  3. #3
    ABW Ambassador
    Join Date
    October 22nd, 2006
    Posts
    1,065
    Using a simple loop in php will be faster.

    For Loop
    Read Table A
    Query Table B using the Table A Keys
    If found
    Update Table A with Table B data
    Endif
    End For Loop

  4. #4
    Grandma broke her coccyx! Uncle Rico's Avatar
    Join Date
    May 8th, 2007
    Location
    North Carolina
    Posts
    2,238
    Looping through each record takes forever.

  5. #5
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    What I do is run a PHP script that checks the data in TableB and compares it to the data in TableA. I create objects representing the data in both tables and serialize them to compare the data. If there is a variation then I update TableA with the data in TableB. This way there are no unnecessary updates to TableA.

    I do this for every product, one merchant at a time. 150k products isn't really that many to loop through though.

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  6. #6
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    The biggest issue to me is that when you do a mass update as a single statement, it can "lock" the table until he statement finishes. If your web site is pulling data from that table, pages will time out. Also, it's easy to miss "deletes" in a mass update. If a product was in the datafeed at one time and no longer is, what do you want to do with it?
    Michael Coley
    Amazing-Bargains.com
     Affiliate Tips | Merchant Best Practices | Affiliate Friendly? | Couponing | CPA Networks? | ABW Tips | Activating Affiliates
    "Education is the most powerful weapon which you can use to change the world." Nelson Mandela

  7. #7
    Grandma broke her coccyx! Uncle Rico's Avatar
    Join Date
    May 8th, 2007
    Location
    North Carolina
    Posts
    2,238
    I ended up giving up on this. I am too limited by memory size from my shared hosting setup.

  8. #8
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    Could always try brute force, add this to the beginning of the script:

    ini_set('max_execution_time', '0');


    Better still is there some way to break it into smaller segments. Like Michael said, you don't want to "lock" up a table.

  9. #9
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    Or could you do the update locally and then upload the finished table to your remote server for your live table? Kind of a pain but could be the easiest way in the end.

  10. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Mysql Table Definition for Google Aff Network Datafeeds
    By Uncle Rico in forum Programming / Datafeeds / Tools
    Replies: 11
    Last Post: December 23rd, 2009, 12:35 PM
  2. Going crazy trying to create or add indexes to MySQL table
    By writerguy in forum Programming / Datafeeds / Tools
    Replies: 9
    Last Post: August 2nd, 2008, 05:07 PM
  3. Replies: 6
    Last Post: February 20th, 2008, 08:05 AM
  4. Quotation marks from datafeed DOUBLED in MySQL table
    By Gib in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: February 25th, 2005, 05:25 PM
  5. Table Problem and Something Else....
    By Doug247 in forum WebMerge (Fourthworld.com)
    Replies: 8
    Last Post: March 9th, 2004, 09:48 AM

Posting Permissions

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