Update ParentID - SQL Server Forums

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

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]

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

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

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]

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

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');

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

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-19 : 11:38:04

So far we have not received feedback from the OP.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources