May 13, 2018 at 2:35 am
I have this business_table
ref_ID name parent_id
-----------------------------
ABC-0001 Amb NULL
PQR-899 boss NULL
tgv-632 pick NULL
yyy-888 xyz NULL
kkk-456 ued NULL
I want to update parent_id of business_table
parent_customer is another table which list the hierarchy of ref_ID and parent_id given below.
To update the parent_id of business_table staps are
1) check ref_id of business_table with ref_id of parent_customer . eg. ref_ID ABC-0001 of business_table match with parent_customer ref_id 1st row 1 ref_id-ABC-0001 opr-656
match found
2) then check parent_id of parent_customer of that matched record which is in this case parent_id opr-656 check with match_table_CM table
match_table_CM table list the ids which we want to match before updating record (we are checking this because of this is CRM id need to check emplpoyee exist of not)
3)match not found then check with parent_id opr-656 of parent_customer with same table parent_customer ref_id , 2nd record found with ref_id opr-656
then pick its parent_id ttK-668 check with match_table_CM match found 1 ttK-668 then update with business_table parent_id other wise check till the
parent_customer ref_ID = parent_id (parent of all) and update that id even if match not found so in this case if match not found then ttK-668 is should be
updated at last
note : - parent_customer table lists a hierarchy of data in which when both ref_id and parent_id are the same means it's the parent of the entire hierarchy.
For example:
4 PQR-899 PQR-899 this is ultimate parent of hierarchy
parent_customer
ID ref_id parent_id
---------------------------
1 ABC-0001 opr-656
2 opr-656 ttK-668
3 ttK-668 ttK-668
4 PQR-899 PQR-899
5 kkk-565 AJY-567
6 AJY-567 UXO-989
7 UXO-989 tgv-632
8 tgv-632 mnb-784
9 mnb-784 qwe-525
10 qwe-525 qwe-525
11 kkk-456 jjj-888
match_table_CM:
id main_id
--------------
1 ttK-668
2 PQR-899
3 tgv-632
4 mnb-784
Expected output
ref_ID name parent_id
-----------------------------
ABC-0001 Amb ttK-668
PQR-899 boss PQR-899
tgv-632 pick qwe-525
yyy-888 xyz NULL
kkk-456 ued jjj-888
May 13, 2018 at 8:12 am
This is a requirement. What is your question?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 13, 2018 at 9:11 am
Phil Parkin - Sunday, May 13, 2018 8:12 AMThis is a requirement. What is your question?
I want to update parent_id of business_table???
this is in discription.
May 13, 2018 at 9:57 am
Gaja - Sunday, May 13, 2018 9:11 AMPhil Parkin - Sunday, May 13, 2018 8:12 AMThis is a requirement. What is your question?I want to update parent_id of business_table???
this is in discription.
That's a
Gaja - Sunday, May 13, 2018 9:11 AMPhil Parkin - Sunday, May 13, 2018 8:12 AMThis is a requirement. What is your question?I want to update parent_id of business_table???
this is in discription.
That isn't question either; it's your requirement with you quesion marks at the end. What is it you need help with? What didn't work with what you tried?
We aren't here to do your work for you, but we're happy to help you trouble shoot any errors you had, or code that isn't working as expected.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 13, 2018 at 11:16 am
I grok your question.
First, you've been around long enough for you to start helping the people that you want help from. Please read and heed the article at the first link under "Helpful Links" in my signature line below before posting another request for help. The following code creates the test tables to support what you posted. This code might be incorrect because I'm just guessing from your narrative.
--=====================================================================================================================
-- Create and populate the test tables.
-- This is not a part of the solution. We're just building test data here.
--=====================================================================================================================
--===== If the test tables exist, drop them.
-- Note that all of the test tables are in TempDB for safety purposes.
IF OBJECT_ID('tempdb.dbo.business_table' ,'U') IS NOT NULL DROP TABLE dbo.business_table;
IF OBJECT_ID('tempdb.dbo.parent_customer','U') IS NOT NULL DROP TABLE dbo.parent_customer;
--===== Identify the database to use.
USE tempdb;
GO
-----------------------------------------------------------------------------------------------------------------------
--===== Create the business_table based on information from the OP
CREATE TABLE dbo.business_table
(
ref_ID VARCHAR(10) NOT NULL
,name VARCHAR(10) NOT NULL
,parent_id VARCHAR(10) NULL
,CONSTRAINT PK_Business_Table PRIMARY KEY CLUSTERED (ref_id)
)
;
--===== Populate the business_table based on information from the OP
INSERT INTO dbo.business_table WITH (TABLOCK)
(ref_id,name)
VALUES ('ABC-0001','Amb')
,('PQR-899','boss')
,('tgv-632','pick')
,('yyy-888','xyz') --This ref_id doesn't even exist in the parent_customer table and so is also bad data.
,('kkk-456','ued') --This ref_id is bad data in the parent_customer table. Notes for parent_customer table.
;
-----------------------------------------------------------------------------------------------------------------------
--===== Create the table based on information from the OP
CREATE TABLE dbo.parent_customer
(
ID INT NOT NULL
,ref_id VARCHAR(10) NOT NULL
,parent_id VARCHAR(10) NOT NULL
)
;
--===== Populate the table with the test data provided by the OP.
INSERT INTO dbo.parent_customer WITH (TABLOCK)
(ID,ref_id,parent_id)
VALUES (1 ,'ABC-0001','opr-656')
,(2 ,'opr-656','ttK-668')
,(3 ,'ttK-668','ttK-668')
,(4 ,'PQR-899','PQR-899')
,(5 ,'kkk-565','AJY-567')
,(6 ,'AJY-567','UXO-989')
,(7 ,'UXO-989','tgv-632')
,(8 ,'tgv-632','mnb-784')
,(9 ,'mnb-784','qwe-525')
,(10,'qwe-525','qwe-525')
,(11,'kkk-456','jjj-888') --Bad data. Parent_ID does not exist as a child and violates the hierarchy.
;
--===== Check the hierarchy from missing children.
-- All parent_id's must also be ref_id's for a proper hierarchical structure.
-- This code found the bad data row above.
SELECT t1.*
FROM dbo.parent_customer t1
WHERE t1.parent_id NOT IN (SELECT ni.ref_id FROM dbo.parent_customer ni)
;
--===== Add keys and relationships necessary to support the "Adjacency List" Hierarchy
ALTER TABLE dbo.parent_customer
ADD CONSTRAINT PK_Parent_Customer PRIMARY KEY CLUSTERED (ref_id)
--Had to comment out the next line because the data breaks the proper structure of the hierarchy
--by allowing orphans to enter the table.
--,CONSTRAINT FK_Parent_ID FOREIGN KEY (parent_id) REFERENCES dbo.parent_customer (ref_id)
,CONSTRAINT AK_Parent_Customer UNIQUE NONCLUSTERED (ID)
;
Now, with that, we can demonstrate the complexities of the problem you've posed with code that works instead of posting a narrative ourselves.
Also, not to go "All Celko" on you but the project that uses the hierarchy that you posted is in deep trouble. Please read the comments in the code below as to what the troubles actually are especially if you and your company intend to do right by your customers. It does what you asked for but I'd never let this or the problematic underlying tables and the total lack of any referential integrity ever make it even to QA, never mind production.
And, yeah... that business table needs to go. You should have a customer table and a hierarchical table. You shouldn't have to update a separate table because everything you need should be in a normalized customer and hierarchical table.
--=====================================================================================================================
-- Solve for the requested output.
-- I say it everywhere but I'll summarize it all here...
--
-- The hierarchy in the parent_customer table has some serious problems.
-- 1. It apparently doesn't have an FK from the parent_id to the ref_id. THAT ALLOWS ORPHANS in this table.
-- 2. It does, in fact, have orphans.
--
-- Also, I'm not understanding the purpose of the match_table_CM table because...
-- 1. It doesn't contain all of the ID's in the requested update and...
-- 2. It contains an ID (mnb-784) that's not in the requested output and...
-- 3. Given 1 and 2 above and the fact that the request output can be resolved without it, seems like a useless
-- table.
--
-- Last but not least, the business_table is seriously at fault because...
-- 1. It contains the orphan that was allowed in the parent_customer table and...
-- 2. It contains a ref_id (yyy-888) that doesn't seem to exist anywhere else.
--
-- With all of that, the referential integrity for this project is totally non-existant and the project seems
-- doomed to silent but deadly errors not to mention making otherwise simple and fast code extremely complex and slow!.
--=====================================================================================================================
--===== We need to refer to the upcoming CTE more than once because of the rule violations inherent in the hierarchy.
-- Rather than beat the hell out of the two tables to include orphaned rows because of the unenforced hierarchical
-- structure, we'll channel the results of the CTE to a temp table and use that as the source in the final update.
-- Note that the following line may be commented out when you turn this into a production stored procedure.
IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL DROP TABLE #MyHead
;
WITH cteFindRoots AS
(--==== Find the names of the children from the business table.
SELECT pc.ref_id
,pc.parent_id
,OriginalBusinessRefId = bt.ref_id
,bt.name
,hpath = CONVERT(VARCHAR(8000),'|' + pc.ref_id + '|')
FROM dbo.parent_customer pc
RIGHT JOIN dbo.business_table bt
ON pc.ref_id = bt.ref_ID
UNION ALL
--==== Traverse the hierarchy to the top level for each child node (ref_id)
SELECT tbl.ref_id
,tbl.parent_id
,cte.OriginalBusinessRefId
,cte.name
,hpath = CONVERT(VARCHAR(8000),hpath + tbl.ref_id + '|')
FROM cteFindRoots cte
JOIN dbo.parent_customer tbl
ON cte.parent_id = tbl.ref_id
WHERE cte.hPath NOT LIKE '%|' + tbl.ref_id + '|%'
) --=== Dump the resulting hierarcy for each node to a Temp Table to avoid running the cte more than once
-- to accommodate the orphans in the hierarchy that are present because the designers of the parent_customer
-- table didn't follow the basic rules for the structure of a proper "Adjacency List" hierarchy.
-- Needless to say but I'll say it anyway, this CRM project is in deep Kimchi!!!
SELECT ref_id = ISNULL(cte.ref_id ,'')
,parent_id = ISNULL(cte.parent_id,'')
,cte.OriginalBusinessRefId
,cte.name
INTO #MyHead
FROM dbo.business_table bt
JOIN cteFindRoots cte
ON bt.ref_ID = cte.OriginalBusinessRefID
;
--===== Update the business_table according to the rules that the OP stated.
UPDATE bt
SET bt.parent_id = NULLIF(h1.parent_id,'')
FROM dbo.business_table bt
JOIN #MyHead h1
ON bt.ref_ID = h1.OriginalBusinessRefID
WHERE h1.ref_id = h1.parent_id
OR NOT EXISTS (SELECT * FROM #MyHead h2 WHERE h1.parent_id = h2.ref_id)
;
--===== Display the final result.
SELECT *
FROM dbo.business_table
;
Seriously... this project has some terrible design and usage problems. You folks need to fix it.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2018 at 1:32 pm
Jeff Moden - Sunday, May 13, 2018 11:16 AMI grok your question.
First, you've been around long enough for you to start helping the people that you want help from. Please read and heed the article at the first link under "Helpful Links" in my signature line below before posting another request for help. The following code creates the test tables to support what you posted. This code might be incorrect because I'm just guessing from your narrative.
--=====================================================================================================================
-- Create and populate the test tables.
-- This is not a part of the solution. We're just building test data here.
--=====================================================================================================================
--===== If the test tables exist, drop them.
-- Note that all of the test tables are in TempDB for safety purposes.
IF OBJECT_ID('tempdb.dbo.business_table' ,'U') IS NOT NULL DROP TABLE dbo.business_table;
IF OBJECT_ID('tempdb.dbo.parent_customer','U') IS NOT NULL DROP TABLE dbo.parent_customer;--===== Identify the database to use.
USE tempdb;
GO
-----------------------------------------------------------------------------------------------------------------------
--===== Create the business_table based on information from the OP
CREATE TABLE dbo.business_table
(
ref_ID VARCHAR(10) NOT NULL
,name VARCHAR(10) NOT NULL
,parent_id VARCHAR(10) NULL
,CONSTRAINT PK_Business_Table PRIMARY KEY CLUSTERED (ref_id)
)
;
--===== Populate the business_table based on information from the OP
INSERT INTO dbo.business_table WITH (TABLOCK)
(ref_id,name)
VALUES ('ABC-0001','Amb')
,('PQR-899','boss')
,('tgv-632','pick')
,('yyy-888','xyz') --This ref_id doesn't even exist in the parent_customer table and so is also bad data.
,('kkk-456','ued') --This ref_id is bad data in the parent_customer table. Notes for parent_customer table.
;
-----------------------------------------------------------------------------------------------------------------------
--===== Create the table based on information from the OP
CREATE TABLE dbo.parent_customer
(
ID INT NOT NULL
,ref_id VARCHAR(10) NOT NULL
,parent_id VARCHAR(10) NOT NULL
)
;
--===== Populate the table with the test data provided by the OP.
INSERT INTO dbo.parent_customer WITH (TABLOCK)
(ID,ref_id,parent_id)
VALUES (1 ,'ABC-0001','opr-656')
,(2 ,'opr-656','ttK-668')
,(3 ,'ttK-668','ttK-668')
,(4 ,'PQR-899','PQR-899')
,(5 ,'kkk-565','AJY-567')
,(6 ,'AJY-567','UXO-989')
,(7 ,'UXO-989','tgv-632')
,(8 ,'tgv-632','mnb-784')
,(9 ,'mnb-784','qwe-525')
,(10,'qwe-525','qwe-525')
,(11,'kkk-456','jjj-888') --Bad data. Parent_ID does not exist as a child and violates the hierarchy.
;
--===== Check the hierarchy from missing children.
-- All parent_id's must also be ref_id's for a proper hierarchical structure.
-- This code found the bad data row above.
SELECT t1.*
FROM dbo.parent_customer t1
WHERE t1.parent_id NOT IN (SELECT ni.ref_id FROM dbo.parent_customer ni)
;
--===== Add keys and relationships necessary to support the "Adjacency List" Hierarchy
ALTER TABLE dbo.parent_customer
ADD CONSTRAINT PK_Parent_Customer PRIMARY KEY CLUSTERED (ref_id)
--Had to comment out the next line because the data breaks the proper structure of the hierarchy
--by allowing orphans to enter the table.
--,CONSTRAINT FK_Parent_ID FOREIGN KEY (parent_id) REFERENCES dbo.parent_customer (ref_id)
,CONSTRAINT AK_Parent_Customer UNIQUE NONCLUSTERED (ID)
;
Now, with that, we can demonstrate the complexities of the problem you've posed with code that works instead of posting a narrative ourselves.Also, not to go "All Celko" on you but the project that uses the hierarchy that you posted is in deep trouble. Please read the comments in the code below as to what the troubles actually are especially if you and your company intend to do right by your customers. It does what you asked for but I'd never let this or the problematic underlying tables and the total lack of any referential integrity ever make it even to QA, never mind production.
And, yeah... that business table needs to go. You should have a customer table and a hierarchical table. You shouldn't have to update a separate table because everything you need should be in a normalized customer and hierarchical table.
--=====================================================================================================================
-- Solve for the requested output.
-- I say it everywhere but I'll summarize it all here...
--
-- The hierarchy in the parent_customer table has some serious problems.
-- 1. It apparently doesn't have an FK from the parent_id to the ref_id. THAT ALLOWS ORPHANS in this table.
-- 2. It does, in fact, have orphans.
--
-- Also, I'm not understanding the purpose of the match_table_CM table because...
-- 1. It doesn't contain all of the ID's in the requested update and...
-- 2. It contains an ID (mnb-784) that's not in the requested output and...
-- 3. Given 1 and 2 above and the fact that the request output can be resolved without it, seems like a useless
-- table.
--
-- Last but not least, the business_table is seriously at fault because...
-- 1. It contains the orphan that was allowed in the parent_customer table and...
-- 2. It contains a ref_id (yyy-888) that doesn't seem to exist anywhere else.
--
-- With all of that, the referential integrity for this project is totally non-existant and the project seems
-- doomed to silent but deadly errors not to mention making otherwise simple and fast code extremely complex and slow!.
--=====================================================================================================================
--===== We need to refer to the upcoming CTE more than once because of the rule violations inherent in the hierarchy.
-- Rather than beat the hell out of the two tables to include orphaned rows because of the unenforced hierarchical
-- structure, we'll channel the results of the CTE to a temp table and use that as the source in the final update.
-- Note that the following line may be commented out when you turn this into a production stored procedure.
IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL DROP TABLE #MyHead
;
WITH cteFindRoots AS
(--==== Find the names of the children from the business table.
SELECT pc.ref_id
,pc.parent_id
,OriginalBusinessRefId = bt.ref_id
,bt.name
,hpath = CONVERT(VARCHAR(8000),'|' + pc.ref_id + '|')
FROM dbo.parent_customer pc
RIGHT JOIN dbo.business_table bt
ON pc.ref_id = bt.ref_ID
UNION ALL
--==== Traverse the hierarchy to the top level for each child node (ref_id)
SELECT tbl.ref_id
,tbl.parent_id
,cte.OriginalBusinessRefId
,cte.name
,hpath = CONVERT(VARCHAR(8000),hpath + tbl.ref_id + '|')
FROM cteFindRoots cte
JOIN dbo.parent_customer tbl
ON cte.parent_id = tbl.ref_id
WHERE cte.hPath NOT LIKE '%|' + tbl.ref_id + '|%'
) --=== Dump the resulting hierarcy for each node to a Temp Table to avoid running the cte more than once
-- to accommodate the orphans in the hierarchy that are present because the designers of the parent_customer
-- table didn't follow the basic rules for the structure of a proper "Adjacency List" hierarchy.
-- Needless to say but I'll say it anyway, this CRM project is in deep Kimchi!!!
SELECT ref_id = ISNULL(cte.ref_id ,'')
,parent_id = ISNULL(cte.parent_id,'')
,cte.OriginalBusinessRefId
,cte.name
INTO #MyHead
FROM dbo.business_table bt
JOIN cteFindRoots cte
ON bt.ref_ID = cte.OriginalBusinessRefID
;
--===== Update the business_table according to the rules that the OP stated.
UPDATE bt
SET bt.parent_id = NULLIF(h1.parent_id,'')
FROM dbo.business_table bt
JOIN #MyHead h1
ON bt.ref_ID = h1.OriginalBusinessRefID
WHERE h1.ref_id = h1.parent_id
OR NOT EXISTS (SELECT * FROM #MyHead h2 WHERE h1.parent_id = h2.ref_id)
;
--===== Display the final result.
SELECT *
FROM dbo.business_table
;Seriously... this project has some terrible design and usage problems. You folks need to fix it.
Thanks for replay man.
you got it right this DB design is not up to the mark but for this single requirement we cannot able to change whole DB.
you are brilliant this is close to answer.
May 14, 2018 at 8:13 am
Gaja - Sunday, May 13, 2018 1:32 PMThanks for replay man.
you got it right this DB design is not up to the mark but for this single requirement we cannot able to change whole DB.you are brilliant this is close to answer.
Heh... there will be future requirements that will be difficult due to the current design. 😉
Curious, though. You say "this is close to the answer".... what was missing?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 14, 2018 at 4:09 pm
LOL @ "not to go 'All Celko' on you"!
May 14, 2018 at 8:33 pm
autoexcrement - Monday, May 14, 2018 4:09 PMLOL @ "not to go 'All Celko' on you"!
😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply