how to update multiple level data

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

how to update multiple level data

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-06 : 12:02:07

I have to update FID, which check on PID, it could be multiple level.
Look at the input/output.

declare @tbl1 table
(ID INT,
PID int,
FID int
)

INSERT INTO @tbl1
SELECT 1,null,null

INSERT INTO @tbl1
SELECT 2,1,null

INSERT INTO @tbl1
SELECT 3,2,null

INSERT INTO @tbl1
SELECT 4,null,null

INSERT INTO @tbl1
SELECT 5,null,null

INSERT INTO @tbl1
SELECT 6,5,null

INSERT INTO @tbl1
SELECT 7,3,null

Output Looks like this:
ID FID
1 1
2 1
3 1
4
5 5
6 5
7 1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-06 : 12:54:54

Can you explain how you want to update the FID...the sample data doesn't tell much.

how is FID 1 for ID=1 and '' for ID = 4

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-06 : 13:05:21

PID=ID
FID=PID

So, if PID value = 1 it means PID is relation with ID' 1

FID=PID=ID

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-06 : 13:09:12

declare @tbl1 table
(ID INT,
PID int,
FID int
)

INSERT INTO @tbl1
SELECT 1,null,null

INSERT INTO @tbl1
SELECT 2,1,null

INSERT INTO @tbl1
SELECT 3,2,null

INSERT INTO @tbl1
SELECT 4,null,null

INSERT INTO @tbl1
SELECT 5,null,null

INSERT INTO @tbl1
SELECT 6,5,null

INSERT INTO @tbl1
SELECT 7,3,null

select * from @tbl1
ID PID FID
1 NULL NULL
2 1 NULL
3 2 NULL
4 NULL NULL
5 NULL NULL
6 5 NULL
7 3 NULL

OUTPUT will be:

1 NULL 1
2 1 1
3 2 1
4 NULL NULL
5 NULL 5
6 5 5
7 3 1

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-07 : 00:13:02

[code]
declare @tbl1 table
(ID INT,
PID int,
FID int
)

INSERT INTO @tbl1
SELECT 1,null,null

INSERT INTO @tbl1
SELECT 2,1,null

INSERT INTO @tbl1
SELECT 3,2,null

INSERT INTO @tbl1
SELECT 4,null,null

INSERT INTO @tbl1
SELECT 5,null,null

INSERT INTO @tbl1
SELECT 6,5,null

INSERT INTO @tbl1
SELECT 7,3,null

;with cte ( id,pid,path)as
(
SELECT id,pid,CONVERT(VARCHAR(MAX),id) from @tbl1 where pid is null
UNION ALL
SELECT t.id,t.pid,c.path+'-'+convert(varchar(max),t.id) from @tbl1 t
inner join cte c on c.id = t.pid
)

update t
set fid = t1.fid
from @tbl1 t
inner join (SELECT id,pid,path,
CASE WHEN CHARINDEX('-',path) = 0 THEN Path ELSE LEFT(path,CHARINDEX('-',path)-1) END AS fid
FROM cte ) t1 on t.id = t1.id

select * from @tbl1

[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-07 : 00:22:27

try like this


declare @tbl1 table 
(ID INT,
PID int,
FID int
)
INSERT INTO @tbl1 SELECT 1,null,null
INSERT INTO @tbl1 SELECT 2,1,null
INSERT INTO @tbl1 SELECT 3,2,null
INSERT INTO @tbl1 SELECT 4,null,null
INSERT INTO @tbl1 SELECT 5,null,null
INSERT INTO @tbl1 SELECT 6,5,null
INSERT INTO @tbl1 SELECT 7,3,null 

;with cte(id , pid, fid)
as
( 
select id , pid,convert(varchar(MAX),id) as fid from @tbl1 where pid is  null

union all
select t.id, t.pid, convert(varchar(MAX),c.fid) + '-'+ convert(varchar(MAX),t.id)
from @tbl1 t inner join cte c on c.id = t.pid
)
update t 
set fid = c.fid
from @tbl1 t 
inner join 
( select id,pid,convert(int,left(fid,1)) as fid from  cte ) c on c.id = t.id 
select * from @tbl1

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-09 : 12:22:12

thanks guys.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-09 : 23:43:51

quote:
Originally posted by rudba

thanks guys.

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources