How to get intermediate value before the last commit?

[newtomysql]

Dear All,
I have a list of product which I run a loop and first I do a insert statement. Following that I am going to update fiels called avarageValue,totalStock and stockValue. So for example first I have a line to insert with productID=123 so once inserted I read what is the current totalStock=10 and stockValue=120 and averageValue=12 of productID=123. So say for this new productID=123 totalStock=1 and stockValue=15. So after inserting this line my update of totalStock=11, stockValue=135 and averageValue=12.28 right. The problem is that when comes to my second,third and so on line with productID=123 where I need to read the latest value but I am not able to read and when I read I get totalStock=10 and stockValue=120 and averageValue=12. Just to add on when I run the loop all the values will be in on single commit where I only commit at the end of the loop? So any solution please ?

 

[r937]

would you still have a problem if you could somehow eliminate the loop?

r937.com | rudy.ca

 

[Kozusnik]

I'd say,
1. only read once and store the values in variables within the loop (if using a programming language)

2. execute the inserts within the loop

4. use a temp table to insert to, update values in the temp table, then update the real table from those values when you're done.

Mark

 

[newtomysql]

Dear Mark,
I am using C#. So that means in my case I need to keep an array is it the keep the temp values ? So in that case I guess I dont need a temp table. Thanks.