Check for null and replace the most recent not null value

Question

The Query needs to Check for null value and replace with the most recent not null value.

For example consider the source data provided below .

Query needs to check the null value and found it in second row, now it should replace the null value with recent not null data and the data is"colmin".

Again the Query need to check the null value and Now it found it in third row and then it will update with recent not null data and the data is again "colmin".

Thanks in advance !

Input

source data    
Colmin    
NULL
NULL
NULL
NULL
columnxy
column99
NULL
NULL
money
NULL
NULL
NULL
start end
NULL

so the output should look like..

output

Ouput data
Colmin    
Colmin    
Colmin    
Colmin    
Colmin    
Colmin    
columnxy
column99
column99
column99
money
money
money
money
start end
start end

Awesome. Thank you for the logic Benjamin, John Bevan.

jack.mike
– jack.mike

2014-11-13 11:29:50 +00:00
Commented Nov 13, 2014 at 11:29 — jack.mike
– jack.mike, Commented Nov 13, 2014 at 11:29

Benjamin M · Accepted Answer · 2014-11-12 21:01:57Z

1

What is "most recent" ? I hope you've got some field to sort on. Row number MUST NOT ALWAYS be in right order! Though I'd use orderField which is will be used to determine the row order.

UPDATE myTable
SET a = (
    SELECT a
    FROM myTable
    WHERE a IS NOT NULL
    AND orderField > (
      SELECT orderField
      FROM myTable
      WHERE a IS NULL
      ORDER BY orderField
      LIMIT 1
    )
    ORDER BY orderField
    LIMIT 1
  )
WHERE a IS NULL
ORDER BY orderField

something like this should do it ... i hope. it's untested.

what it does:

Find "orderField" for first row with a = null
Find first a value (!= null) AFTER orderField from 1.
Update a value from 1. with value from 2.

It should also work more easy:

UPDATE myTable t1
SET t1.a = (
    SELECT t2.a
    FROM myTable t2
    WHERE t2.a IS NOT NULL
    AND t2.orderField > t1.orderField
    ORDER BY t2.orderField
    LIMIT 1
  )
WHERE t1.a IS NULL

edited Nov 12, 2014 at 21:01

answered Nov 12, 2014 at 20:53

Benjamin M

24.7k34 gold badges135 silver badges211 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JohnLBevan · Accepted Answer · 2014-11-12 20:54:37Z

0

Try this:

declare @input table (id bigint not null identity(1,1), OutputData nvarchar(16) null)
insert @input select 'Colmin'    
insert @input select NULL
insert @input select NULL
insert @input select NULL
insert @input select NULL
insert @input select 'columnxy'
insert @input select 'column99'
insert @input select NULL
insert @input select NULL
insert @input select 'money'
insert @input select NULL
insert @input select NULL
insert @input select NULL
insert @input select 'start end'
insert @input select NULL

--where a's value is null, replace with a non-null from b
select coalesce(a.OutputData, b.OutputData) 
--return all rows from input as a
from @input a
--return all non-null values as b
left outer join
(
    select id, OutputData
    from @input
    where OutputData is not null
) b
--the non-null value should come before the null value (< or <= works equally here)
on b.id <= a.id
--and there shouldn't be a non-null value between b and a's records (i.e. b is the most recent before a)
and not exists (
    select top 1 1 
    from @input c 
    where c.id between b.id and a.id 
    and c.id <> b.id
    and c.OutputData is not null
)
--display the output in the required order
order by a.id

answered Nov 12, 2014 at 20:54

JohnLBevan

24.8k16 gold badges107 silver badges201 bronze badges

1 Comment

JohnLBevan Over a year ago

ps. your question implies some sort of order to the data; I've assumed it's an ID field giving this order; however the above should work for any column(s) which you're able to programmatically sort.

DeadZone · Accepted Answer · 2014-11-13 15:49:50Z

0

If your table is a single column, then you will need to either run multiple queries in a loop or use a cursor to iterate through the table, one row at a time. (not efficient.)

If you have some kind of ID column, then you can use a correlated subquery to find the first non-null value. Something like...

Update A
Set    TextCol = (SELECT Top 1 TextCol From MyTable B Where B.TextCol IS NOT NULL AND B.IDCol < A.IDCol ORDER BY IDCol DESC)
FROM   MyTable A
WHERE  TextCol IS NULL

edited Nov 13, 2014 at 15:49

answered Nov 12, 2014 at 20:54

DeadZone

1,6891 gold badge18 silver badges34 bronze badges

1 Comment

DeadZone Over a year ago

The logic works just fine. The syntax had a small problem... The update statement needed an alias to work with SQL Server. Here's the SqlFiddle as proof: sqlfiddle.com/#!3/c26bd5/5

Collectives™ on Stack Overflow

Check for null and replace the most recent not null value

The Query needs to Check for null value and replace with the most recent not null value.

Input

output

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

The Query needs to Check for null value and replace with the most recent not null value.

Input

output

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related