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
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 From 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
WITH CTE AS ( Select *, max(Date) over(order by ID) AS newdate from table1) UPDATE CTE SET Date = newdate
In 2012 and above:
WITH s AS ( SELECT id, date, COALESCE( LEAD(id) OVER (ORDER BY id), ( SELECT MAX(id) + 1 FROM t ) ) nid FROM t WHERE date IS NOT NULL ) MERGE INTO t USING s ON t.id > s.id AND t.id < s.nid WHEN MATCHED THEN UPDATE 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.
UPDATE m FROM mytable m SET date = ( SELECT TOP 1 date 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 GO 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) ; SELECT A.MyDate,b.LastDate, A.Id FROM T1 A CROSS APPLY( Select Top 1 MyDate as LastDate From T1 where Id <= A.Id and Mydate is not null order by id desc ) B