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 2012 Forums
 Transact-SQL (2012)
 Update ParentID

Author  Topic 

samir.first
Starting Member

34 Posts

Posted - 2013-09-18 : 04:38:20
I need Query To Update ParentId Depends on Code After Update
the Table is:
ID Code ParentID
1 11 Null
2 1101 1
3 1102 1
4 1103 1
5 12 NULL
6 1201 5
7 1202 5
8 120201 7
else .........
I need Best Query

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 04:47:51
[code]DECLARE @Sample TABLE
(
ID INT NOT NULL,
Code VARCHAR(100) NOT NULL,
ParentID INT
);

INSERT @Sample
(
ID,
Code
)
VALUES (1, '11'),
(2, '1101'),
(3, '1102'),
(4, '1103'),
(5, '12'),
(6, '1201'),
(7, '1202'),
(8, '120201');

-- SwePeso
UPDATE s
SET s.ParentID = f.ID
FROM @Sample AS s
CROSS APPLY (
SELECT MIN(x.ID)
FROM @Sample AS x
WHERE x.Code = LEFT(s.Code, LEN(s.Code) - 2)
) AS f(ID);

SELECT *
FROM @Sample;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-18 : 05:13:44
[code]UPDATE A
SET ParentID = B.ID
FROM @Sample A
JOIN @Sample B
ON LEFT(A.code, LEN(A.code) - 2) = B.code[/code]
Go to Top of Page

samir.first
Starting Member

34 Posts

Posted - 2013-09-18 : 07:31:48
thank you SwePeso but number (2) in ( LEN(s.Code) - 2)) is declare not static
11
1101
11010001
1101000100001
else
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 07:51:01
[code]-- Prepare sample data
DECLARE @Sample TABLE
(
ID INT NOT NULL,
Code VARCHAR(100) NOT NULL,
ParentID INT
);

INSERT @Sample
(
ID,
Code
)
VALUES (1, '11'),
(2, '1101'),
(9, '11010001'),
(0, '1101000100001'),
(3, '1102'),
(4, '1103'),
(5, '12'),
(6, '1201'),
(7, '1202'),
(8, '120201');

-- SwePeso
UPDATE s
SET s.ParentID = f.ID
FROM @Sample AS s
CROSS APPLY (
SELECT TOP(1) x.ID
FROM @Sample AS x
WHERE s.Code LIKE x.Code + '%'
AND LEN(s.Code) > LEN(x.Code)
ORDER BY LEN(x.Code) DESC
) AS f(ID);

-- Display result
SELECT *
FROM @Sample
ORDER BY Code;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-18 : 08:35:41
[code]update s
set parentID = (select top 1 id
from @sample
where code < s.code
and len(code) < len(s.code)
order by code desc)
from @sample s;[/code]
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 10:12:30
Sigmas and mine solution behave differently.
-- Prepare sample data
DECLARE @Sample TABLE
(
ID INT NOT NULL,
Code VARCHAR(100) NOT NULL,
ParentID INT,
Sigmas INT
);

INSERT @Sample
(
ID,
Code
)
VALUES (1, '11'),
(2, '1101'),
(9, '11010001'),
(0, '1101000100001'),
(3, '1102'),
(4, '1103'),
(5, '12'),
(6, '1201'),
(7, '1202'),
(8, '9999');

-- SwePeso
UPDATE s
SET s.ParentID = f.ID
FROM @Sample AS s
CROSS APPLY (
SELECT TOP(1) x.ID
FROM @Sample AS x
WHERE s.Code LIKE x.Code + '%'
AND LEN(s.Code) > LEN(x.Code)
ORDER BY LEN(x.Code) DESC
) AS f(ID);

UPDATE s
SET Sigmas = (
SELECT TOP(1) x.ID
FROM @Sample AS x
WHERE x.Code < s.Code
AND LEN(x.Code) < LEN(s.Code)
ORDER BY x.Code DESC
)
FROM @Sample AS s;

-- Display result
SELECT *
FROM @Sample
ORDER BY Code;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-18 : 12:28:44
Base on sample data posted by open poster the roots node are two characters.
You added a node called root that is out of the root range! an orphan node ('9999'), the orphan node needs parent node ('99')
If you add following row to the table my solution will work fine.

INSERT INTO @Sample (ID, code) VALUES (99, '99');
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 14:10:59
Yes, they do. But we can't know.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-19 : 11:38:04
So far we have not received feedback from the OP.
Go to Top of Page
   

- Advertisement -