update values of Null column between particular numbers

Question

1.00/5 (1 vote)

See more:

I have column in my sql database named as S-No.
Which has values like following :

ID         S-No
____       ____
 1          1
____       ____
 2          2
____       ____
 3         Null
____       ____
 4         Null
____       ____
 5          3
____       ____
 6         Null
____       ____
 7         Null
____       ____
 8         Null
____       ____
 9         Null
____       ____
 10        Null
____       ____
 11         4
____       ____
 12        Null

.
.

Now, i want to write query in such a way that, which S-No column contains Null between 1 to 2, update there value to 1..... And which has Null between 2 To 3, update there value to 2... And so on... eg. 3 to 4 -- update as 3, 4 to 5 --- update as 4, 5 to 6...... I hope you will suggest me the query.. i am not able to write.. Frown | :( i hope you guyz will help.

Result Should be like this :

ID         S-No
____       ____
 1          1
____       ____
 2          2
____       ____
 3          2
____       ____
 4          2
____       ____
 5          3
____       ____
 6          3
____       ____
 7          3
____       ____
 8          3
____       ____
 9          3
____       ____
 10         3
____       ____
 11         4
____       ____
 12         4

.
.

Posted 19-Feb-15 7:56am

Harpreet_125

Updated 19-Feb-15 8:26am

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 19-Feb-15 14:26pm

Why didn't you simply add detail to your earlier question?
http://www.codeproject.com/Questions/878096/update-column-values-which-are-null?arn=0

2 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)

Wendelius · Answer 1 · 2015-02-19T08:00:00

Solution 1

You can try something like

SQL

UPDATE MyTableName a
SET [S-No] = (SELECT MAX([S-No])
              FROM MyTableName b
              WHERE b.Id < a.Id
              AND   b.[S-No] IS NOT NULL)
WHERE [S-No] IS NULL

Posted 19-Feb-15 8:00am

Wendelius

Comments

/\jmot 23-Feb-15 4:25am

The easy one. :) +5

Wendelius 23-Feb-15 14:13pm

Thank you !

Madhu@sql · Answer 2 · 2015-02-22T19:42:00

begin

set nocount on

create table #temp
(
row_id int ,
row_value int

)

insert into #temp values(1,1)
insert into #temp values(2,2)
insert into #temp values(3,null)
insert into #temp values(4,null)
insert into #temp values(5,3)
insert into #temp values(6,null)
insert into #temp values(7,null)
insert into #temp values(8,null)
insert into #temp values(9,null)
insert into #temp values(10,null)
insert into #temp values(11,4)
insert into #temp values(12,null)

select * from #temp

declare @row_id int=0
declare @row_value int
declare @temp_row_value int

while(1=1)
begin

select top 1 @row_id=row_id,
@row_value=row_value
from #temp
where row_id>@row_id
order by row_id

if(@@ROWCOUNT=0)

begin
break
end

if @row_value is null
begin

update #temp
set row_value=@temp_row_value
where row_id=@row_id

end

else
begin
select @temp_row_value=@row_value
end

end

select * from #temp
drop table #temp

end