0

I'm trying to update my parent table column if my child table column has all the same values for a single ParentId.

T1 - Parent

|      ParentId       |    StatusId      |
|---------------------|------------------|
|          1          |       1          |
|---------------------|------------------|
|          2          |       1          |
|---------------------|------------------|

T2 - Child

|      ChildId        |     ParentId     |    StatusId      |
|---------------------|------------------|------------------|
|          1          |       1          |        0         |
|---------------------|------------------|------------------|
|          2          |       1          |        0         |
|---------------------|------------------|------------------|
|          3          |       1          |        0         |
|---------------------|------------------|------------------| 
|          4          |       2          |        1         |
|---------------------|------------------|------------------| 
|          5          |       2          |        0         |
|---------------------|------------------|------------------|

If T2 status column has all 0's for ParentId 1 then set status in T1 to 0 else if there is a 1 status for any ParentId in T2 then set the status in T1 to 1 or do nothing.

Desired Result after update statement:

T1 - Parent

|      ParentId       |    StatusId      |
|---------------------|------------------|
|          1          |       0          |
|---------------------|------------------|
|          2          |       1          |
|---------------------|------------------|

T2 - Child

|      ChildId        |     ParentId     |    StatusId      |
|---------------------|------------------|------------------|
|          1          |       1          |        0         |
|---------------------|------------------|------------------|
|          2          |       1          |        0         |
|---------------------|------------------|------------------|
|          3          |       1          |        0         |
|---------------------|------------------|------------------| 
|          4          |       2          |        1         |
|---------------------|------------------|------------------| 
|          5          |       2          |        0         |
|---------------------|------------------|------------------|

I know can perhaps do something like this using an update and maybe a while loop but I'm not quite sure how to construct it. Any help is appreciated.

CursiosAsEver69
  • 3
  • 1
  • UPDATE PT SET PT.StatusId = CASE                    WHEN PT.StatusId = 1 THEN 1                  WHEN CT.StatusId IN (0,1) THEN 1                END FROM ParentTable PT JOIN ChildTable CT ON PT.ParentID = CT.ParentId – CursiosAsEver69 Oct 12 '17 at 18:33
  • As a general rule, try to avoid while loops. – Sean Sherman Oct 12 '17 at 20:53

1 Answers1

1

This can be done using an Update From Select

Update T1
    Set StatusID = newStatusID
From
(
    Select ParentID as changedParentID
        , max(StatusID) as newStatusID
    From T2
    Group By ParentID
)x
Where T1.ParentID = x.changedParentID
And T1.StatusID <> x.newStatusID
chrisuae
  • 1,092
  • 7
  • 8
  • This is a very simple but great solution. Sometimes the answer to problems can be so small but you find your time looking for harder solutions when you don't need to. – CursiosAsEver69 Oct 13 '17 at 13:33