Sql-server – UPDATE using CTE

ctesql serversql-server-2016

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:

enter image description here

This is what it looks like before the update as the cleared_run_id is the only column that needs to be updated:

enter image description here

How would I need to modify the statement to achieve that?

Best Answer

It seems - although it's not all clear - that you want a running total per fqcn, so the GROUP BY you have doesn't make sense.

For the update, it appears that we are looking at a gaps-and-islands problem. Each "island" or group is identified / ends by a row that has current_value = 0. There are a few ways to solve gaps-and-islands problems, you can look at the questions of the site that have been tagged with gaps-and-islands.

Something like this might do what you are after. I use a second CTE to separate the rows into groups:

 ;
WITH 
  cte AS
  ( SELECT fqcn, run_id, cleared_run_id, diff_value, 
           current_value = SUM(diff_value) OVER 
               ( PARTITION BY fqcn
                 ORDER BY run_id 
                 ROWS UNBOUNDED PRECEDING )
    FROM sonar_resource_diff
  ),
  cte2 AS
  ( SELECT *, 
           grp = COUNT(CASE WHEN current_value = 0 THEN 1 END) OVER
               ( PARTITION BY fqcn
                 ORDER BY run_id 
                 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    FROM cte
  )
UPDATE sr 
SET sr.cleared_run_id = mark.run_id 
FROM cte2 AS sr
     JOIN cte2 AS mark
     ON  mark.fqcn = sr.fqcn 
     AND mark.grp = sr.grp
     AND mark.current_value = 0
 ;
Related Question