I am working on a PHP project in which a mysql table needs to be updated with lots of rows in a single update.

A set of parameters will be assigned for a product.

Please find my present steps below:
1. Delete all rows with the product_id in the table
2. Insert data into it. (Say 10no of rows)

I am looking forward to have the following setup
1. Create a temporary table.
2. Insert data into the temp table.
3. Update the original table

Which method do you think is better? Which one uses less resource?

4 Years
Discussion Span
Last Post by an00p

I'd say that the first on consume less resources, but the second is more reliable.

And I will suggest a third:
- Delete only the records that will no longer exists
- Update the records that already exists but need to be updated
- Insert the new records

The implementation is a little more complex, but the result is the best in my opnion.

Also, you should create the sql stataments and execute them only once. In example:

DELETE FROM MyTable WHERE Product_ID = 1 AND Detail_ID IN ( 1, 2, 3, 4 );

UPDATE MyTable SET Value = 'Something' WHERE Product_ID = 1 and Detail_ID = 5;
UPDATE MyTable SET Value = 'Something Else' WHERE Product_ID = 1 and Detail_ID = 6;

INSERT INTO MyTable(Product_ID, Detail_ID, Value)
    (1, 7, 'Value...'), (1, 8, 'Value...'), (1, 9, 'Value...');

Edited by AleMonteiro


Thanks for your help :) . Would definitly try the third one.
With regards to sql statements, I always try to make the sql statements to execute them only once.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.