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?

  • 2
    I don't quite follow from the screen shot which rows should be updated, where new cleared_run_id values should come from, or even whether the screen shot is before the update, after the update, or what you want as the desired result. Instead of the query that doesn't work and a screen shot, can you please provide sample data before the update, and how you want the data to look like after the update? PLEASE READ. Apr 10 '18 at 19:19
  • @AaronBertrand Thanks for replying. The screenshot is done after the update, cleared_run_id are all NULL at the beginning, to show what I am trying to achieve. cleared_run_id s should be updated with run_id values where running total, current_value is 0, in a manner shown above.
    – Victor
    Apr 10 '18 at 19:31
  • 2
    Please show before and after, not just after. And it is easier if you provide the data as insert statements, not screen shots, so we can work with it on our own systems instead of transcribing from tiny columns in a screen shot. Apr 10 '18 at 19:32
  • 1
    @Victor please provide sample table data (with INSERT statements) as Aaron commented, so others can answer. It's also not clear if the table has a current_value column or not. Apr 10 '18 at 19:52
  • 1
    You can use dbfiddle.uk to load sample data. Apr 10 '18 at 20:00

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:

  cte AS
  ( SELECT fqcn, run_id, cleared_run_id, diff_value, 
           current_value = SUM(diff_value) OVER 
               ( PARTITION BY fqcn
                 ORDER BY run_id 
    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 
    FROM cte
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
  • Not sure how to use dbfiddle but the snippet seems to give me exactly what I need.
    – Victor
    Apr 10 '18 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.