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 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.*” ?