Post reply

How to update a null column in previous rows when following rows have a non null value for this column

  • November 15, 2018 at 5:16 pm

    #378631

    Something like IF Col A (of ROW with id of 100) IS NOT NULL, then   Check all previous rows and set Col A to SomeValue when it IS NULL. But leave Col A as is in the rows following ROW with id of 100.

    I tried with cte's but was not able to get the desired results.

    Hope someone here can help me sort it out.

  • November 15, 2018 at 6:14 pm

    #2013759

    jaimepremy - Thursday, November 15, 2018 5:16 PM

    Something like IF Col A (of ROW with id of 100) IS NOT NULL, then   Check all previous rows and set Col A to SomeValue when it IS NULL. But leave Col A as is in the rows following ROW with id of 100.

    I tried with cte's but was not able to get the desired results.

    Hope someone here can help me sort it out.

    I'm not sure if this is what you really want, but it's what you've asked for:
    UPDATE a
       SET a.ColA = 'SomeValue'
      FROM myTable a
     WHERE EXISTS(SELECT *
                    FROM myTable b
                   WHERE b.Id = 100
                     AND b.ColA is not null)
       AND a.Id < 100
       AND a.ColA IS NULL

  • November 16, 2018 at 9:00 am

    #2013802

    Thank u for your answer. I ended up adding a flag column to the table and set a flag on each row matching the conditions needed. Then I did an update based upon the flag value.

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

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