CODEWITHSUNDEEP
sqlsql-server
Shaun Brown
Shaun Brown

Reputation: 11

SQL - update query - update to next date value that is not NULL

I have a bunch of values that are currently on dates with NULL value (i.e. no data available on those particular dates).

How would I go about updating those values to the next date where there is data available?

I have a select query currently which highlights all values that lie on a date with NULL value (or false data defined by a value of less than 0):

select * from table1 a 
left join table2 b on a.id=b.id and a.date=b.date --joins dates table to main data set

where a.id in (select c.id from table3 c
left join table4 d on c.id=d.id where c.value = 000000) -- sub query identifying sub set of data I want to use as 'id' list

and a.date is not NULL and a.date > '1900-01-01' --a.date not NULL just identifies illegitimate date values that I don't want to see
and (b.value is NULL or b.value < 0) --identifies legitimate values that fall on dates where there are NULL values or false dates

So this query gives me all values from a chosen data set that fall on dates with false data or NULL values. There are a few more 'where' and 'and' variables I've used in the query but this hopefully gives a good base of understanding.

I would like to update all of these values to the next date in the future that is not NULL (i.e. has legit data).

Just a small example of what I'm thinking: update table1 set date = (assume there would be some sort of select sub query here to define next date value that is not NULL).

Just another note to take into consideration: the next date that the value is not NULL is dynamic - it could be 2 days from given date but it could be 2 years.

Upvotes: 1

Views: 1266

Answers (2)

Kemal AL GAZZAH
Kemal AL GAZZAH

Reputation: 1047

/*I would create a variable table @mytab in which I will put sample sample data
with dates and null*/
--Kamel Gazzah
--07/03/2019
declare @mytab as table(id int identity(1,1),mydate  date)

insert into @mytab values('01/01/2018')
insert into @mytab values(NULL)
insert into @mytab values('01/05/2018')
insert into @mytab values('01/07/2018')
insert into @mytab values('01/08/2018')
insert into @mytab values(NULL)
insert into @mytab values(NULL)
insert into @mytab values(NULL)
insert into @mytab values('01/08/2018')

select * from @mytab


--First Method with **OUTER APPLY**
update t1 set mydate=t2.mydate
--select t1.*,t2.mydate 
from @mytab t1
OUTER APPLY (select top 1 * from @mytab where mydate is not null and id > t1.id order by mydate) t2 
where t1.mydate is null



--SCOND METHOD WITH **LEFT OUTER JOIN**
update ta set mydate=tc.mydate
--select ta.id,tc.mydate 
from @mytab ta 
inner join(
select id1,min(id2) id2 from(
select t1.id id1,t2.id id2,t2.mydate from @mytab t1
left outer join @mytab t2 on t2.id > t1.id and t2.mydate is not null
where t1.mydate is null) v group by id1) tb on ta.id=id1
inner join @mytab tc on  tb.id2=tc.id

select * from @mytab

Upvotes: 2

Daniel Brughera
Daniel Brughera

Reputation: 1651

You can solve it using apply 

UPDATE T
SET Date = N.Date
FROM yourTable T
OUTER APPLY (
    SELECT TOP 1 Date FROM YourTable 
    WHERE ........
    ORDER BY ..........
) N
WHERE T.Date IS NULL

Upvotes: 0

Related Questions