Member Avatar for an00p

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?

  • 2 Contributors
  • 2 Replies
  • 267 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by an00p
Member Avatar for AleMonteiro

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
Member Avatar for an00p

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.