Match and set child's ID based on condition-CodePudding

I've got a primary incremental ID column and have to find and set all its childs (in ParentID column) based on values from two other columns (Condition1 and Condition2) Started ParentID always has Condition2 = 1 (and the same value in Condition1 column)

Initial table

 ---------------------------------------------
|  ID |  ParentID  | Condition1 | Condition2 |
 ---------------------------------------------
|  1  |       null |      1000  |          1 |
|  2  |       null |      1000  |       null |
|  3  |       null |      1000  |       null |
|  4  |       null |      2000  |          1 |
|  5  |       null |      2000  |       null |
|  6  |       null |      2000  |       null |
|  7  |       null |      3000  |          1 |
|  8  |       null |      3000  |       null | 
|  9  |       null |      3000  |       null |
 ---------------------------------------------

Desired Output

 ---------------------------------------------
|  ID |  ParentID  | Condition1 | Condition2 |
 ---------------------------------------------
|  1  |          1 |      1000  |          1 |
|  2  |          1 |      1000  |       null |
|  3  |          1 |      1000  |       null |
|  4  |          4 |      2000  |          1 |
|  5  |          4 |      2000  |       null |
|  6  |          4 |      2000  |       null |
|  7  |          7 |      3000  |          1 |
|  8  |          7 |      3000  |       null | 
|  9  |          7 |      3000  |       null |
 ---------------------------------------------

Current code returns only one row for each new ID

update u 
set u.ParentID = u.ID
from [db].[dbo].[tbl] u
  inner join [db].[dbo].[tbl]  on 
        u.Condition2 = 1 and u.Condition1 = u.Condition1

CodePudding user response：

I think one intuitive way of doing this is

UPDATE [db].[dbo].[tbl] u
SET u.ParentID = (SELECT id FROM [db].[dbo].[tbl] u2 WHERE u2.condition1 = u.condition1 and u2.condition2 = 1)

I don't mean that this is better than what you're trying to do, just that I think it's very intuitive and easy to understand if you're having issues.

I think the solution you are looking for is:

update u 
set u.ParentID = u2.ID
from [db].[dbo].[tbl] u
  inner join [db].[dbo].[tbl] u2  on 
        u2.Condition2 = 1 and u.Condition1 = u2.Condition1

The important differences here are that I have given the both tables in the join-to-self a name (u and u2). You don't want to set u.ParentID = u.ID (as in your question), you want to set u.ParentID = u2.ID (note the 2). Similarly, you don't want to join the tables on u.condition1 = u.condition1 (since that is always true in this example) or u.condition2 = 1 (since you're applying that condition to the table you're updating, not the table you joined). Even though it is a join-to-self, you need to be clear about which table you are referencing. u in your query refers to the table being updated, but not the table on the right-side of the join.