SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP/MySQL - Bulk Update in a single query?

    Greetings,

    I have a script that allows members to import/export up to 10,000 products. I was able to build a script to bulk import 10,000 products with a SINGLE MySQL insert statement by building an array instead of looping through 10,000 insert queries.

    When members export the files, they are given the id of each item already listed. So they can edit their item descriptions, prices and 20 different columns, and re-import them. To do this, I need to use the MySQL UPDATE. The problem is I have to loop through 10,000 items and do 10,000 Update queries. I'm not sure how to get it into a single Update function

    Is there a way to do build up the updates into an array and Update all of the listings in a single query?

    Thanks
    Kind regards

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by peppy View Post
    Is there a way to do build up the updates into an array and Update all of the listings in a single query?
    upload the updates into a table, then do a joined update (details in da manual)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply and pointing me towards a solution.

    I am starting with my main "products" table and have now created an empty, but duplicate table structure called "update_products". During an bulk edit import, I now do my regular bulk insert of the items to the update_products table. ( I am currently running into an error during insertion: Duplicate entry '2147483647' for key 'PRIMARY' , but I'll probably figure this out later).

    Here is what I have for joining update:
    $sql = "UPDATE products LEFT JOIN update_products ON update_products.id = products.id SET products.global_cat_id = update_products.global_cat_id WHERE seller = '$seller'";
    mysql_query($sql) or die(mysql_error());

    I haven't tested this ( I will once I deal with the error code tomorrow for inserting items into update_products. The Update query here is updating just one column "global_cat_id". How can I update the entire row like a copy and paste? Like: "SET products.* = update_products.*" ?

    Thanks
    Kind regards

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by peppy View Post
    How can I update the entire row ...
    SET t1.a = t2.a, t1.b = t2.b, t1.c = t2.c, ...

    you have to do all columns one at a time like that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help r937, it looks like it's working perfectly!

    Kind regards


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
  •