CODEWITHSUNDEEP
sqlsql-serversql-server-2008
jack.mike
jack.mike

Reputation: 3

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.

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

Upvotes: 0

Views: 229

Answers (3)

DeadZone
DeadZone

Reputation: 1680

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

Upvotes: 0

Benjamin M
Benjamin M

Reputation: 24527

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:

  1. Find "orderField" for first row with a = null
  2. Find first a value (!= null) AFTER orderField from 1.
  3. 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

Upvotes: 1

JohnLBevan
JohnLBevan

Reputation: 24410

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

Upvotes: 0

Related Questions