I am trying to use an update corelated query which is expected to do the following:
The outer update query updates a field and the sub query based on the outer query fetches, using a select, the updatable field from a previous row in the table. Based on what is returned by the previous row the inner query computes on this field and passes the same to the outer query for updation. And this continues for all other rows meeting a certain criteria.
In short i am using the corelated sub query to update an updatable field whose value is calculated and updated in the same corelated query in some other iteration.
Following is the query i am using in postgres:
update sample_tab2 a set stock=initialstock + (percent/100)*(select b.stock from sample_tab2 b
where a.region=b.region and b.period=(a.period - cast('1 week' as interval) * 1)) where region='Asia';
The result of this is:
if the stock field has some initial values in it then it calculates those values and updates the same field for some other row but if the stock field's value is calculated in the same query in some other iteration and used in some other row for updation then it doesnt get the latest value, as updated in the corelated query, and updates with old value that existed before the corelated query executed.
Although I am using postgres but my question is general:
Does a corelated update sub query update the fields in the DB only after the execution of the whole query that includes all iterations of the corelated query and during this time it maintains a cache where the fields' values that were updated in the corelated query execution are kept. So if one queries the fields updated by the corelated query, within the same user session with the DB, but during the course of queries even through a corelated query then these would return old values. And these values can be seen only after the query has executed completely. I hope it makes sense...
If what i have stated above is true then what can be the solution for this?
Else please share your views and possible solutions to the problem. A solution may exist using cursors but I want to explore if I can do the above expected using just one query.