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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Same question and different approach
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

fl0at
Starting Member

Turkey
5 Posts

Posted - 05/04/2004 :  07:19:08  Show Profile
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.


Edited by - fl0at on 05/04/2004 07:25:17

harshal_in
Aged Yak Warrior

India
633 Posts

Posted - 05/04/2004 :  07:51:48  Show Profile  Visit harshal_in's Homepage  Send harshal_in a Yahoo! Message
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
Go to Top of Page

fl0at
Starting Member

Turkey
5 Posts

Posted - 05/04/2004 :  08:04:27  Show Profile
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


Edited by - fl0at on 05/04/2004 08:10:24
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/04/2004 :  10:39:14  Show Profile  Visit nr's Homepage
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.
Go to Top of Page

fl0at
Starting Member

Turkey
5 Posts

Posted - 05/05/2004 :  02:39:53  Show Profile
Yes I want d root of tree but it is not null it is 0.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000