SQL Server Forums
Profile | Register | 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.05 seconds. Powered By: Snitz Forums 2000