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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Update ParentID
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

samir.first
Starting Member

Egypt
28 Posts

Posted - 09/18/2013 :  04:38:20  Show Profile  Reply with Quote
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

Sweden
30116 Posts

Posted - 09/18/2013 :  04:47:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



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

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/18/2013 :  05:13:44  Show Profile  Reply with Quote
UPDATE A
SET ParentID = B.ID
FROM @Sample A
JOIN @Sample B
ON LEFT(A.code, LEN(A.code) - 2) = B.code

Edited by - sigmas on 09/18/2013 08:36:52
Go to Top of Page

samir.first
Starting Member

Egypt
28 Posts

Posted - 09/18/2013 :  07:31:48  Show Profile  Reply with Quote
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

Sweden
30116 Posts

Posted - 09/18/2013 :  07:51:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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;



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

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/18/2013 :  08:35:41  Show Profile  Reply with Quote
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;

Edited by - sigmas on 09/18/2013 08:37:52
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/18/2013 :  10:12:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Belarus
172 Posts

Posted - 09/18/2013 :  12:28:44  Show Profile  Reply with Quote
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

Sweden
30116 Posts

Posted - 09/18/2013 :  14:10:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Belarus
172 Posts

Posted - 09/19/2013 :  11:38:04  Show Profile  Reply with Quote
So far we have not received feedback from the OP.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000