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 a dataset. Zero must be updated with previous values ​​between two non-zeros. Sequence_id will not necessarily be in a specific order, but Dex_ID follows in ascending order.

The result should look like this:

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

Here is another solution for this:

Link to the demo file → rexterster

    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

Answer 2

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 3

Itzik Ben-Gan The Last non NULL Puzzle.

: db < >

CREATE TABLE #T 
(
    Sequence_ID integer NULL, 
    Dex_ID integer PRIMARY KEY
);

INSERT #T
    (Sequence_ID, Dex_ID)
VALUES
    (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),
    (NULL, 14),
    (NULL, 15);

WITH 
    Grouped AS
    (
        SELECT
            T.Sequence_ID,
            T.Dex_ID,
            TargetDex_ID =
                MAX(IIF(T.Sequence_ID IS NULL, NULL, T.Dex_ID)) OVER (
                    ORDER BY T.Dex_ID ASC
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        FROM #T AS T
    ),
    Sequenced AS
    (
        SELECT
            G.Sequence_ID,
            G.Dex_ID,
            NewSequence_ID =
                MAX(G.Sequence_ID) OVER (
                    PARTITION BY G.TargetDex_ID
                    ORDER BY G.Dex_ID
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        FROM Grouped AS G
    )
UPDATE Sequenced
SET Sequenced.Sequence_ID = Sequenced.NewSequence_ID;

SELECT
    T.Sequence_ID,
    T.Dex_ID 
FROM #T AS T
ORDER BY
    T.Dex_ID;

 
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
          3 |     14
          3 |     15

• ,
• TOP gbn answer
• SQLCLR .
• Microsoft, LAG IGNORE NULLS

.

SQL Server .

UPDATE (Transact-SQL):

UPDATE , , UPDATE . UPDATE , , OUTPUT.

. , "quirky update" , .