Update intermediate rows

Hi I have a data set like this:

Date                    ID
2015-06-17 15:57:00.000 1
NULL                    2
NULL                    3
NULL                    4
NULL                    5
NULL                    6
2015-06-17 15:58:00.000 7
NULL                    8
NULL                    9
NULL                    10
NULL                    11
NULL                    12
2015-06-17 17:50:04.000 13
NULL                    14
2015-06-17 17:51:00.000 16
NULL                    17
2015-06-17 17:52:03.000 19
NULL                    20
2015-06-17 17:52:04.000 22
NULL                    23
2015-06-17 17:52:04.000 25
NULL                    26
2015-06-17 17:52:04.000 28
NULL                    29

As you can see, they are in sequence (ascending), but many dates are NULL

  • nested query vs jOINs
  • echo image according to a condition
  • Can the script for median be generalised and put into a function type structure
  • Failed to create an IDataAdapter object. This provider may not be fully supported with the Execute SQL Task
  • Changing one datatype to another in all columns of SQL Server in Java
  • Conversion failed when converting date and/or time from character string while inserting datetime
  • I want to update the NULL entries to have the nearest prior date/time

    So rows 2 thru 6 should get the date time from row ID 1 and
    8 thru 12 should get datetime from ID 7, etc.

    I’m sure there’s an easy way to do this with a single update statement, but I’d drawing a blank

    4 Solutions collect form web for “Update intermediate rows”

    You should be able to use a correlated subquery to do this; the following query seemed to work when I tried it (see fiddle below), but be sure to have a backup handy in case I’m wrong 🙂

    update t1
    set date = (select max(date) from your_table where id <= t1.id and date is not null)
    from your_table t1
    where t1.date is null

    Sample SQL Fiddle

    Note that this might give some strange results if the dates aren’t sequential which I assumed they are.

    Also, if you’re using a version 2012+ of SQL Server then using the max() function as a windowed function (max(date) over (...)) is a better option. The details are presented in another answer.

    You can use MAX() OVER() effectively here

    Update table1 
        set t1.date = t2.date
              table1 t1
              INNER JOIN (Select id, max(date) over(order by id) date from table1)    t2
              ON t1.id = t2.id


    As Quassnoi points out this depends on the dates being non-descreasing.

    Martin Smith contens that this formulation is better

           Select *, 
                  max(Date) over(order by ID) AS newdate 
           from table1) 
    SET Date = newdate

    In 2012 and above:

    WITH    s AS
            SELECT  id, date,
                            LEAD(id) OVER (ORDER BY id),
                            SELECT  MAX(id) + 1
                            FROM    t
                    ) nid
            FROM    t
            WHERE   date IS NOT NULL
    INTO    t
    USING   s
    ON      t.id > s.id
            AND t.id < s.nid
    SET     date = s.date

    This still has to read the whole table twice, but does it in a more efficient way than a nested loop for each record over and over, especially if the gaps are large.

    Earlier versions:

    UPDATE  m
    FROM    mytable m
    SET     date =
            SELECT  TOP 1
            FROM    mytable mi
            WHERE   mi.id < m.id
                    AND mi.date IS NOT NULL
            ORDER BY
                    id DESC
    WHERE   date IS NULL
        Drop Table T1
    Create table T1( MyDate DateTime, Id int );
    Insert T1 Values
    ('2015-06-17 15:57:00.000',  1),
    (NULL, 2), 
    (NULL, 3), 
    (NULL, 4), 
    (NULL, 5), 
    (NULL, 6), 
    ('2015-06-17 15:58:00.000', 7), 
    (NULL, 8), 
    (NULL, 9), 
    (NULL, 10), 
    (NULL, 11), 
    (NULL, 12), 
    ('2015-06-17 17:50:04.000', 13), 
    (NULL, 14), 
    ('2015-06-17 17:51:00.000', 16), 
    (NULL, 17), 
    ('2015-06-17 17:52:03.000', 19), 
    (NULL, 20), 
    ('2015-06-17 17:52:04.000', 22), 
    (NULL, 23), 
    ('2015-06-17 17:52:04.000', 25), 
    (NULL, 26), 
    ('2015-06-17 17:52:04.000', 28), 
    (NULL, 29) ;
        A.MyDate,b.LastDate, A.Id
    FROM T1 A
                Select Top 1 MyDate as LastDate  
                From T1 
                where Id <= A.Id and Mydate is not null
                order by id desc   
                ) B
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.