Updating from previous row until next value is found

Question

Updating from previous row until next value is found

Cheryl McLaughlin-385812

SSC Eights!

Points: 928
More actions
November 10, 2020 at 2:10 pm

#3806380

I am trying to create an update that will update a value from previous row until the next none null value is found then update with that value.
For instance in my table there are 2 columns SeqNum and AcctNum. The SeqNum will always start with 0000003 and the AcctNum will have a value and it could have 3 rows with it but the following rows the SeqNum and AcctNum will be null. Using the 3 row example the next SeqNum would be 0000006 with the AcctNum populated. Once it sees the SeqNum 0000006 it needs to update the null value for AcctNum for the next set of null values until the next non null SeqNum is found until it reaches the end.
The table would like before the update
After the update, the AcctNum would populate with AcctNun from previous SeqNum and then update with AcctNum associated to the next SeqNum when it is found

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

This is not possible as it stands, because the NULL rows do not have any ordering.

What I mean is, there is nothing to associate the first two NULL rows with 123 and the final 4 with 567, as tables do not have any default order.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Once you have some ordering in place, one solution is as follows:

DROP TABLE IF EXISTS #SomeTab;

CREATE TABLE #SomeTab
(
    rn INT NOT NULL
   ,SeqNum VARCHAR(20) NULL
   ,ActNum INT NULL
);

INSERT #SomeTab
(
    rn
   ,SeqNum
   ,ActNum
)
VALUES
('1', '0000003', 123)
,('2', NULL, NULL)
,('3', NULL, NULL)
,('4', '0000006', '567')
,('5', NULL, NULL)
,('6', NULL, NULL)
,('7', NULL, NULL)
,('8', NULL, NULL);

SELECT *
FROM #SomeTab st
ORDER BY st.rn;

UPDATE st
SET st.ActNum = NotNull.ActNum
FROM #SomeTab st
    CROSS APPLY
(
    SELECT TOP (1)
           st2.ActNum
    FROM #SomeTab st2
    WHERE st2.SeqNum IS NOT NULL
          AND st2.rn < st.rn
    ORDER BY st2.rn DESC
) NotNull
WHERE st.ActNum IS NULL;

SELECT *
FROM #SomeTab st
ORDER BY st.rn;

This reply was modified 4 years ago by Phil Parkin. Reason: Fixed slight logic error in query

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin