Update Problem - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Update Problem

Author

Topic

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2009-06-28 : 07:49:06

I have a table with the following data:

CREATE TABLE tbl
(
id int,
Dsc varchar(100)
)

id Dsc
-- ------
1 Desc1
NULL Desc2
NULL Desc3
2 Desc4
NULL Desc5
3 Desc6
4 Desc7
NULL Desc8

I want to update null ids with the nearest non-null id upper than that row and the result should be:

id Dsc
-- ------
1 Desc1
1 Desc2
1 Desc3
2 Desc4
2 Desc5
3 Desc6
4 Desc7
4 Desc8

Can anybody tell me how can i do it? thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-28 : 07:55:49

[code]
update t
set id = (select max(id) from tbl x where x.id is not null and x.Dsc < t.Dsc)
from tbl t
where t.id is null
[/code]

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

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2009-06-28 : 08:01:30

Thank you for reply but dsc values are not in an ascending order. it was just a sample. Data could be like:

id Dsc
-- ------
1 Desc8
NULL Desc3
NULL Desc9
2 Desc2
NULL Desc5
3 Desc1
4 Desc6
NULL Desc4

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-28 : 08:15:59

then how do you determine the sequence ? bear in mind that records are not stored in any particular sequence or order in the database. You determine the required ordering or sequence of record retrieve during SELECT statement with the ORDER BY clause.

So what is your ORDER BY to obtain the following sets of records ?


id Dsc
-- ------
1 Desc8
NULL Desc3
NULL Desc9
2 Desc2
NULL Desc5
3 Desc1
4 Desc6
NULL Desc4

select id, Dsc from yourtable ORDER BY ? ? ?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-28 : 13:10:34

quote:
Originally posted by daidaluus

Thank you for reply but dsc values are not in an ascending order. it was just a sample. Data could be like:

id Dsc
-- ------
1 Desc8
NULL Desc3
NULL Desc9
2 Desc2
NULL Desc5
3 Desc1
4 Desc6
NULL Desc4

do you have anyother unique valued column in your table? may be an identity or datetime audit column?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources