I am trying to update the single table with 4 columns
(run_id, fqcn, diff_value, cleared_run_id) with the following snippet:
WITH cte AS ( SELECT diff.fqcn, diff.run_id, diff.cleared_run_id, diff.diff_value, SUM(diff.diff_value) OVER (PARTITION BY diff.fqcn ORDER BY diff.fqcn, diff.run_id ROWS UNBOUNDED PRECEDING) AS current_value FROM sonar_resource_diff diff GROUP BY diff.fqcn, diff.run_id, diff.diff_value, diff.cleared_run_id ) UPDATE sr SET sr.cleared_run_id = sr.run_id FROM sonar_resource_diff sr INNER JOIN cte ON (cte.fqcn = sr.fqcn AND cte.run_id = sr.run_id) WHERE cte.current_value = 0
The problem is that it only updates rows where running total is 0, while I need to update that row AND rows where
run_id < row where running total value is 0 so it will look like this in the end:
This is what it looks like before the update as the
cleared_run_id is the only column that needs to be updated:
How would I need to modify the statement to achieve that?