update Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update Query help

Author

Topic

gfaryd
Starting Member

27 Posts

Posted - 2011-04-19 : 09:30:38

dear all

i m using sql server 2000

following is sample table and data (actual table contains more than 30000 records)

create table sample(new_id smallint, reference varchar(10))

insert into sample (1,'North')
insert into sample (2,null)
insert into sample (3,null)
insert into sample (4,null)
insert into sample (5,null)
insert into sample (6,'south')
insert into sample (7,null)
insert into sample (8,'Dec')
insert into sample (9,null)
insert into sample (10,null)
insert into sample (11,'wtr')
insert into sample (12,null)
insert into sample (13,null)

i want to update all null values equal to the preceding first not null value till another different value

e.g

after updation if i run query

select * from sample order by new_id

the result should come like this

New_id reference
1 North
2 North
3 North
4 North
5 North
6 south
7 south
8 Dec
9 Dec
10 Dec
11 wtr
12 wtr
13 wtr

i m not configuring how to update this

regards,

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-19 : 09:46:56

update tbl
set reference = (select top 1 reference from tbl t2 where t2.id < t.id order by t2.id desc)
from tbl t
where tempdb.reference is null

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gfaryd
Starting Member

27 Posts

Posted - 2011-04-19 : 23:33:39

[quote]Originally posted by nigelrivett

update tbl
set reference = (select top 1 reference from tbl t2 where t2.id < t.id order by t2.id desc)
from tbl t
where tempdb.reference is null

dear nigel ,
when i run ur query its give me an error on tempdb.reference that column is not referenced. when i user t.reference instead of tempdb.reference then it updates only one single preceding null value agaist all data , how to update all rows in single execution

Farid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-19 : 23:41:05

change to


where tempdbt.reference is null

KH
[spoiler]Time is always against us[/spoiler]

gfaryd
Starting Member

27 Posts

Posted - 2011-04-20 : 00:30:33

[quote]Originally posted by khtan

change to


where tempdbt.reference is null

i have already done that but it only updates one null row at a time.
i want to do this in a single go as i have large data set

Farid

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-20 : 00:53:55

create table sample(new_id smallint, reference varchar(10))

insert into sample values (1,'North')
insert into sample values (2,null)
insert into sample values (2,null)
insert into sample values (3,null)
insert into sample values (4,null)
insert into sample values (5,null)
insert into sample values (6,'south')
insert into sample values (7,null)
insert into sample values (8,'Dec')
insert into sample values (9,null)
insert into sample values (10,null)
insert into sample values (11,'wtr')
insert into sample values (12,null)
insert into sample values (13,null)

select * from sample

update t
set reference = (select top 1 reference from sample t2 where t2.new_id < t.new_id and t2.reference is not null order by t2.new_id desc)
from sample t
where t.reference is null

select * from sample

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources