Reputation: 57

SQL Server : Update Query

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?

Upvotes: 4

Answers (3)

user9481273

Reputation: 1

The following solution uses Itzik Ben-Gan's first solution in his article The Last non NULL Puzzle.

Run it at: db<>fiddle

Data

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);

Solution

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;

Output

 
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

Other solutions

Itzik's second method based on concatenation
TOP as in gbn's answer
Use a SQLCLR function to read the data in order and compute the desired value per row.
Vote for Microsoft to implement LAG with IGNORE NULLS

Avoid the variable assignment solution.

SQL Server only guarantees variable assignment behaviour when a single row is updated.

From the documentation UPDATE (Transact-SQL):

Variable names can be used in UPDATE statements to show the old and new values affected, but this should be used only when the UPDATE statement affects a single record. If the UPDATE statement affects multiple records, to return the old and new values for each record, use the OUTPUT clause.

This is the only intended and supported usage. People have experimented with ways to make this "quirky update" reliable over the years, but it is an absolute minefield and simply best avoided completely.

Upvotes: 0

gbn

Reputation: 432657

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

Upvotes: 6

hkravitz

Reputation: 1385

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

Upvotes: 2