Microsoft SQL Server 2005
--
Questions
--
Followers
Top Experts
SQL Update of ParentId column when copying rows in same table
Hi Everyone,
Hope you can help!
I have a stored procedure that I am running to copy rows from a table and then insert them as new rows for cloning functionality. This works fine apart from some of these rows have parent Id's. The parent-child relationship exists within the same table and will only ever be related in one instance.
However when copying these rows, while generating a new table Id, the parent child relationship is lost as the parent Id is still the same as the copied rows. To help solve this I have created a column in the table that is populated by the previous column Id. Once the stored procedure has run, I then look to update the relevant parentId's with the new ones.
I have attached the stored procedure code, and also a screenshot of the data so you can see how the table looks. So the stored procedure would run and copy these rows inserting them in to the same table. This is fine, I think it's just trying to solve the SQL to update the rows with the relevant parentId.
Hope you can help! Thanks in advance guys.
Hope you can help!
I have a stored procedure that I am running to copy rows from a table and then insert them as new rows for cloning functionality. This works fine apart from some of these rows have parent Id's. The parent-child relationship exists within the same table and will only ever be related in one instance.
However when copying these rows, while generating a new table Id, the parent child relationship is lost as the parent Id is still the same as the copied rows. To help solve this I have created a column in the table that is populated by the previous column Id. Once the stored procedure has run, I then look to update the relevant parentId's with the new ones.
I have attached the stored procedure code, and also a screenshot of the data so you can see how the table looks. So the stored procedure would run and copy these rows inserting them in to the same table. This is fine, I think it's just trying to solve the SQL to update the rows with the relevant parentId.
Hope you can help! Thanks in advance guys.
--Insert the values from the selected payroll into the same table
INSERT INTO WF_MilestoneRule --Id column automatically generated
(ParentId
,MilestoneType
,MilestoneName
,MilestoneDescription
,RuleTypeId
,RuleValueId
,RuleValue
,Time
,AdminFolderId
,ExternallyVisible
,MilestoneCreatedById) -- Id of the column that has been copied from
SELECT
ParentId
,MilestoneType
,MilestoneName
,MilestoneDescription
,RuleTypeId
,RuleValueId
,RuleValue
,Time
,@FolderId
,ExternallyVisible
,Id
FROM WF_MilestoneRule
WHERE AdminFolderId = @AdminFolderId
--Set ParentId
UPDATE WF_MilestoneRule SET [ParentId] = (SELECT [Id] FROM WF_MilestoneRule WHERE [MilestoneCreatedById] = ParentId)
WHERE MilestoneCreatedById IN (SELECT [Id] FROM WF_MilestoneRule WHERE [AdminFolderId] = @AdminFolderId)
AND ParentId IS NOT NULL
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Try this sinlge INSERT which should suffice..
If I have understood your requirements clearly, then column names are correct in the subquery..
Else just change the column names alone.
If I have understood your requirements clearly, then column names are correct in the subquery..
Else just change the column names alone.
--Insert the values from the selected payroll into the same table
INSERT INTO WF_MilestoneRule --Id column automatically generated
(ParentId
,MilestoneType
,MilestoneName
,MilestoneDescription
,RuleTypeId
,RuleValueId
,RuleValue
,Time
,AdminFolderId
,ExternallyVisible
,MilestoneCreatedById) -- Id of the column that has been copied from
SELECT
(SELECT t2.Id FROM WF_MilestoneRule t2 WHERE t1.MilestoneCreatedById = t2.ParentId)
,MilestoneType
,MilestoneName
,MilestoneDescription
,RuleTypeId
,RuleValueId
,RuleValue
,Time
,@FolderId
,ExternallyVisible
,Id
FROM WF_MilestoneRule t1
WHERE t1.AdminFolderId = @AdminFolderId
Hi, thanks for this. After changing my stored procedure to run what you have suggested, it gave me the results displayed in my screenshot.
It has copied the rows, however Row Id's 13 and 14, should now have a parentId of 8 and 9 respectively. This is similar to the issue I was having with my original SQL. I can see what you are trying to do though and to be honest if it could be achieved without my update I think I could understand it a bit better.
New-Query-Results.JPG
It has copied the rows, however Row Id's 13 and 14, should now have a parentId of 8 and 9 respectively. This is similar to the issue I was having with my original SQL. I can see what you are trying to do though and to be honest if it could be achieved without my update I think I could understand it a bit better.
New-Query-Results.JPG
>> It has copied the rows, however Row Id's 13 and 14, should now have a parentId of 8 and 9 respectively. This is similar to the issue I was having with my original SQL. I can see what you are trying to do though and to be honest if it could be achieved without my update I think I could understand it a bit better.
Ok, I have simplified your INSERT and UPDATE into a single INSERT statement and hence behaving as it is earlier..
Still I am not clear on the logic how Row ID 13 and 14 need to be updated with ParentID 8 and 9.
Kindly explain that so that appropriate operations can be done..
Ok, I have simplified your INSERT and UPDATE into a single INSERT statement and hence behaving as it is earlier..
Still I am not clear on the logic how Row ID 13 and 14 need to be updated with ParentID 8 and 9.
Kindly explain that so that appropriate operations can be done..
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
If you look at rows Id 6 it has a parentId of 1, that is because when inserting into the table it is linked to Id 1 and similarly for row Id 7 the parent is row 2.
When the rows are copied and inserted into the new rows, the parentId's remain the same as before. As Id 6 is now Id 13 the parent needs to change accordingly, so the ParentId for this row should now change from 1 to 8 to reflect this.
Sorry I know it is confusing, but when these are copied the relationship should be reassigned to reflect the new row id's generated. If you look at the data, the user will set a minor to link with a major element in the MilestoneType column. This is where we establish the relationship. Each AdminFolderId is unique so the copied rows will never be related to what it has copied from, so I need to regenerate the relationship.
Hope this helps?
When the rows are copied and inserted into the new rows, the parentId's remain the same as before. As Id 6 is now Id 13 the parent needs to change accordingly, so the ParentId for this row should now change from 1 to 8 to reflect this.
Sorry I know it is confusing, but when these are copied the relationship should be reassigned to reflect the new row id's generated. If you look at the data, the user will set a minor to link with a major element in the MilestoneType column. This is where we establish the relationship. Each AdminFolderId is unique so the copied rows will never be related to what it has copied from, so I need to regenerate the relationship.
Hope this helps?
Hope I got it correct this time:
--Insert the values from the selected payroll into the same table
INSERT INTO WF_MilestoneRule --Id column automatically generated
(ParentId
,MilestoneType
,MilestoneName
,MilestoneDescription
,RuleTypeId
,RuleValueId
,RuleValue
,Time
,AdminFolderId
,ExternallyVisible
,MilestoneCreatedById) -- Id of the column that has been copied from
SELECT
0
,MilestoneType
,MilestoneName
,MilestoneDescription
,RuleTypeId
,RuleValueId
,RuleValue
,Time
,@FolderId
,ExternallyVisible
,Id
FROM WF_MilestoneRule t1
WHERE t1.AdminFolderId = @AdminFolderId
--Set ParentId
UPDATE WF_MilestoneRule
SET [ParentId] = (SELECT t2.ID
FROM WF_MilestoneRule t2 join WF_MilestoneRule t3 on t2.ParentID = t3.MilestoneCreatedById
WHERE t1.MilestoneCreatedById = t2.ID )
FROM WF_MilestoneRule t1
WHERE t1.AdminFolderId = @FolderId
AND t1.ParentID = 0
When running the stored procedure I get this error ...
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
It did insert rows into the table, but gave all parentid's a value of 0.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
It did insert rows into the table, but gave all parentid's a value of 0.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
>> It did insert rows into the table, but gave all parentid's a value of 0.
Yes, I changed it to 0 using the INSERT statement to easily identify newly Inserted records..
And try this fixed UPDATE statement..
Yes, I changed it to 0 using the INSERT statement to easily identify newly Inserted records..
And try this fixed UPDATE statement..
UPDATE WF_MilestoneRule
SET [ParentId] = (SELECT t2.ID
FROM WF_MilestoneRule t2 join WF_MilestoneRule t3 on t2.ParentID = t3.MilestoneCreatedById
WHERE t1.MilestoneCreatedById = t2.ID
AND t2.ParentID is not null )
FROM WF_MilestoneRule t1
WHERE t1.AdminFolderId = @FolderId
AND t1.ParentID = 0
You will have strange issues in case the child is copied but not the parent in the same operation.
Imagine for whatever reason, you had these records:
ID,Parent
1,null
4,1
And a cloning function copies only row 4 (there is a where clause that has excluded 1).
What is the parent of the new record (say 10)?
Imagine for whatever reason, you had these records:
ID,Parent
1,null
4,1
And a cloning function copies only row 4 (there is a where clause that has excluded 1).
What is the parent of the new record (say 10)?
rrjegan, it's failing because there are multiple matches in your subquery. Use min,max or even top(1) in the subquery to get rid of the error.
But even solving the query/syntax error, not sure about the correctness.
But even solving the query/syntax error, not sure about the correctness.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
>> rrjegan, it's failing because there are multiple matches in your subquery
And that's the reason why I have included this in the fixed one..
AND t2.ParentID is not null
Since there are multiple Null values, its failing and the modified one should work..
Let DaiWilliams check that one..
And that's the reason why I have included this in the fixed one..
AND t2.ParentID is not null
Since there are multiple Null values, its failing and the modified one should work..
Let DaiWilliams check that one..
Thanks for all the comments and help so far guys. Unfortunately I am still getting the same error as earlier and it is still updating it to 0.
What if I inserted a 0 into the parentId column instead on leaving it as NULL for the Major items? Will that help? There are only ever 5 of these major rows per instance, the Minor items only ever link to one of the Major items.
Cyberkiwi - the cloning function will copy all of the rows linked to an instance.
What if I inserted a 0 into the parentId column instead on leaving it as NULL for the Major items? Will that help? There are only ever 5 of these major rows per instance, the Minor items only ever link to one of the Major items.
Cyberkiwi - the cloning function will copy all of the rows linked to an instance.
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Fantastic thankyou very much !!!!!!!!!!!!!
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Microsoft SQL Server 2005
--
Questions
--
Followers
Top Experts
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.