SQL Server : Update Query

Question

Sequence_ID Dex_ID
1       1
null    2
null    3
2       4
null    5
null    6
3       7
null    8
null    9
4       10
null    11
null    12
3       13

Above is the data set. The null should be updated by the preceding values between the two not nulls. The sequence_id will not necessarily be in a specific order but the Dex_ID follows an ascending order.

The output should look like :

Sequence_ID Dex_ID
1   1
1   2
1   3
2   4
2   5
2   6
3   7
3   8
3   9
4   10
4   11
4   12
3   13

Any suggestions?

Answer 1

SQL Fiddle example

UPDATE
    f1
SET
    Sequence_ID = x.Sequence_ID
FROM
    MyTable f1
    CROSS APPLY
    (SELECT TOP (1) Sequence_ID
     FROM MyTable f2 
     WHERE f2.Dex_ID < f1.Dex_ID 
       AND f2.Sequence_ID IS NOT NULL 
       AND f1.Sequence_ID IS NULL
     ORDER BY f2.Dex_ID desc
     ) x
WHERE
    f1.Sequence_ID IS NULL

Answer 2

Here is another solution to accomplish that:

Link to Demo -> rextester

    DECLARE @i INT 
    ;WITH T (Sequence_ID ,Dex_ID)
    AS 
    (
    SELECT 1     ,  1
    UNION ALL
    SELECT null  ,  2
    UNION ALL
    SELECT null  ,  3
    UNION ALL
    SELECT 2     ,  4
    UNION ALL
    SELECT null  ,  5
    UNION ALL
    SELECT null  ,  6
    UNION ALL
    SELECT 3     ,  7
    UNION ALL
    SELECT null  ,  8
    UNION ALL
    SELECT null  ,  9
    UNION ALL
    SELECT 4     ,  10
    UNION ALL
    SELECT null  ,  11
    UNION ALL
    SELECT null  ,  12
    UNION ALL
    SELECT 3     ,  13
    )
    SELECT * 
    INTO #T
    FROM T
    /*The fill up of the null*/
    UPDATE #T 
    SET @i = Sequence_ID = CASE WHEN  Sequence_ID is null THEN @i Else Sequence_ID END

    SELECT *
    FROM #T

    DROP TABLE #T