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?

Recommended Answers

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 …

Jump to Post

All 2 Replies

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...');

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.