Same question and different approach

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

Old Forums

CLOSED - General SQL Server

Same question and different approach

Author

Topic

fl0at
Starting Member

5 Posts

Posted - 2004-05-04 : 07:19:08

Hi all
Suppose you have a table like
create table temp_one
(
catid int,
parentid int
d int
)

lets assume we have the following inserts like
catid parentid d
1 0
2 1
3 2
4 3
5 4

Here comes the question
With a cursor how can i update d field with the catid having parentid = 0
Output should be like
catid parentid d
1 0 1
2 1 1
3 2 1
4 3 1
5 4 1

Thnx.

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-05-04 : 07:51:48

why do u need a cursor?
update temp_one set d=(select catid from temp_one where parentid=0)

He is a fool for five minutes who asks , but who does not ask remains a fool for life!<N>

http://www.sqldude.4t.com

fl0at
Starting Member

5 Posts

Posted - 2004-05-04 : 08:04:27

I need cursor cause the depth is not static it is dynamic also
In your update query i have to assume that there is only one 0 parentid but there wont be only one parentid having value 0.
there will be many parentid with 0 like

The result must be like below..

catid parentid d
1 0 (1)
10 0 (10)
2 1 (1)
3 2 (1)
4 3 (1)
5 4 (1)
14 10 (10)
15 14 (10)

Thanks for the reply.

quote:
Originally posted by harshal_in

why do u need a cursor?
update temp_one set d=(select catid from temp_one where parentid=0)

He is a fool for five minutes who asks , but who does not ask remains a fool for life!<N>

http://www.sqldude.4t.com

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-04 : 10:39:14

You mean you want d to be the root of the tree?
Assuming d starts off as null.

update tbl set d = catid where parentid = 0
while @@rowcount > 0
update tbl
set d = p.d
from tbl c
join parent p
on p.catid = c.parentid
where c.d is null
and p.d is not null

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

fl0at
Starting Member

5 Posts

Posted - 2004-05-05 : 02:39:53

Yes I want d root of tree but it is not null it is 0.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources