How to update only NULL value with recent value, but if there is any value in a row, skip it

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Let's consider I have one table:-

Table Name :- TblA
Only one Column:- Val (varchar)

Val...........................................val
------O/P AS---> -------------
NULL......................................NULL
NULL......................................NULL
NULL......................................NULL
P1............................................P1
NULL......................................P1
NULL......................................P1
NULL......................................P1
NULL......................................P1
P2............................................P2
NULL......................................P2
NULL......................................P2
NULL......................................P2
P3............................................P3
NULL......................................P3
NULL......................................P3
NULL......................................P3
NULL......................................P3
NULL......................................P3
NULL......................................P3

Thanks

What I have tried:

I tried with Using LEAD & LAG function, but both are available in sql 2012. Don't want to use cursor for this. And not want to script as If 1st row and value =NULL then leave, don't update.

Smart solution without using LEAD & LAG, without cursor

Posted 22-Mar-16 0:48am

StackQ

Updated 22-Mar-16 14:04pm

v6

Add a Solution

Comments

John C Rayan 22-Mar-16 7:58am

What you say in your requirement doesn't reflect your solution. For example , for the sixth record the previous value is NULL not P1.

CHill60 22-Mar-16 8:36am

In the title where the OP has said "if not null prev one" I think was their attempt to say "I need the last non-null value prior to this row"

John C Rayan 22-Mar-16 8:40am

I agree and I have developed the solution based on that assumption. thanks CHill60

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Accepted Answer · 2016-03-22T02:06:00

I know this look complex but I'll explain:

SQL

--Test Data i used
declare @mytest table(id int not null identity(1,1) primary key,val varchar(max) null)
insert into @mytest(val)
values 
(null),
(null),
('p1'),
(null),
(null),
(null),
(null),
('p2'),
(null),
(null),
('p2'),
(null),
(null),
(null),
('p3'),
(null) 
;

--Common table expressions (CTE)
with mycte as (
  select id, val, row_number () over (order by id) as roworder
  from @mytest)
, mycte2 as(
  select id, val, roworder
  from mycte
  where roworder = 1

  union all 

  select t.id, isnull(t.val,l.val), t.roworder
  from mycte t
  inner join mycte2 l on t.roworder = l.roworder+1
  )

  --select result
  select * from mycte2

If you run that block, you will see the desired outcome.

We can ignore the test data. That's just me trying to recreate the situation.

The Common Table Expressions are what are interesting

Lets look at the first one:

SQL

with mycte as (
  select id, val, row_number () over (order by id) as roworder
  from @mytest)

The id's I create are in sequence, but I could not say if yours are. I hope that the data you have is in some sort of order (date, id, etc) because we need to give the list a sequential iterative number to make our live so much easier. That's what row_number does here. It puts the items in order and numbers them 1,2,3,..,,n-1,n. We can use this sequence in something called a recursive CTE.

Here is the recursive CTE:

SQL

mycte2 as(
  select id, val, roworder
  from mycte
  where roworder = 1

  union all 

  select t.id, isnull(t.val,l.val), t.roworder
  from mycte t
  inner join mycte2 l on t.roworder = l.roworder+1
  )

This is where all the magic happens. In the first 'select' we take the first item in the list (roworder = 1). Then we union that with the same table joined to this CTE. Get the next item where roworder is this+1, but then it has to recalculate the recursion again, and so on until there is no item where t.roworder = l.roworder+1.

Each time we attach the next item to the current list we use ISNULL(t.val,l.val), where t represents 'this' record and l is 'last' record. If this record.val is null then use last record.val.

I don't think I explained this very well. Just try it out and see if it works ^_^

John C Rayan · Accepted Answer · 2016-03-22T02:36:00

This solution is tested in SQL 2008 R and works as expected

with myrecords(rowid, val) as
(
  select ROW_NUMBER() over (order by (select 1)) as rowId , Value from NullManipulation
)
,myrecords1(rowid, val) as
(
  select ROW_NUMBER() over (order by (select 1)) as rowId , Value from NullManipulation
)
UPDATE mr1
SET val = mr.val
FROM myrecords1 mr1 , myrecords mr 
WHERE mr.rowid < mr1.rowid and not exists(select rowid from myrecords myr where myr.rowid > mr.rowid and myr.rowid < mr1.rowid and myr.val is not null ) and mr1.val is NULL

The table creation is

SQL

CREATE TABLE [dbo].[NullManipulation](
	[Value] [varchar](10) NULL
)

The inserts are
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values ('P1');
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values ('P2');
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values ('P3');
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);
insert into NullManipulation values (NULL);

The result is

C++

Value
NULL
NULL
NULL
P1
P1
P1
P1
P1
P2
P2
P2
P2
P3
P3
P3
P3
P3
P3
P3

CHill60 · Accepted Answer · 2016-03-22T02:50:00

Solution 1 is rather nice, but here is an alternative non-recursive version.
It also uses two CTEs - the second one runs a self-join on the first which is a good alternative for LAG/LEAD in versions of SQL Server prior to 2012 (which you may find useful for other stuff)

SQL

;WITH CTE AS
(
	select id, val ,ROW_NUMBER() OVER(ORDER BY id) AS rn
	from TblA where val is not null
),CTE2 AS
(
	SELECT CTE.id, CTE.val, isnull(nxt.id, (select max(id) from TblA) + 1) as nxt
	FROM CTE
	LEFT OUTER JOIN CTE nxt ON nxt.rn = CTE.rn + 1
)
update t set val = t1.val
from TblA t
join CTE2 t1 on t.id > t1.id and t.id < t1.nxt

Points to note:
I've used the same data as Andy Lanng in solution 1 - i.e. we need an id column with the data in order to determine the "previous" record and to order by. If you can't change the table schema then do something like this

SQL

create table #temp (id int identity(1,1), val varchar(max))
insert into #temp select * from TblA

and use #temp in the queries above.

CTE just gets the id of any rows that DO contain data
CTE2 gets the id of the NEXT row that contains data so we end up with a (temporary) table looking like

Id      Val     Nxt
4	P1	9
9	P2	13
13	P3	17

The ON clause in the final part of the query links up the records in the gaps

StackQ · Accepted Answer · 2016-03-22T07:51:00

Using solution from Andy & John- I am able to get o/p with below query for "SELECT"

SQL

with mycte 
as 
(
  select  val, row_number () over (order by (select 1)) as roworder  from TMP
), 
mycte2 as
(
  select val, roworder  from mycte  where roworder = 1 
  union all  
  select isnull(P.val,C.val), P.roworder
  from mycte P
  inner join mycte2 C on P.roworder = C.roworder+1
  )
 SELECT VAL FROM MYCTE2

And for update I used logic of Andy, John & chill:-

SQL

WITH CTE1
AS
(
  SELECT  val, ROW_NUMBER () OVER (ORDER BY (SELECT 1)) AS roworder  FROM TMP
),
CTE2
AS
(
  select val, roworder  from CTE1  where roworder = 1 
  union all  
  select isnull(P.val,C.val), P.roworder
  from CTE1 P
  inner join CTE2 C on P.roworder = C.roworder+1
  )
 UPDATE CTE1 SET VAL=CTE2.VAL FROM CTE2 
 WHERE CTE1.roworder=CTE2.roworder

How to update only NULL value with recent value, but if there is any value in a row, skip it

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0