0

Scenario: I need to update all children from a family based on the values populated from a single child. So if Child A has a city of 'London' for example, I would want Child B and C to have 'London' applied to their city field as well.

Example:

Family/Child:

enter image description here

Child:

enter image description here

Final DE:

enter image description here

In the example above I would want ClientId = 2 and ClientId = 3 to have the value applied to ChildId = 1, since they all have the same familyId. I keep getting twisted in the logic because we need to include a childId in the resulting data extension as it's the primary key, so I don't know how to group the results by familyId and apply to all childIds. Any help is appreciated.

Improve this question

1 Answer 1

Reset to default
1

I believe the solution would be to do a subquery that contains value pairs of FamilyId and City. Then you would Join this with your main query matching on FamilyId - so that each entry with that FamilyId will get the adjoining City.

For Example:

SELECT  a.ChildID
        , a.FamilyId
        , b.City
FROM [yourDE] a
LEFT JOIN (
    SELECT  FamilyId
            City
    FROM [yourDE]
    WHERE City IS NOT NULL
    AND City != ''
) b
ON a.FamilyId = b.FamilyId
Improve this answer
3
  • 1
    Do you think adding WHERE City != NULL in the subquery be beneficial so that you're only getting FamilyId/City pairs that are valid? Also, I'm curious if children in a family can have their own city assignment, so you could have some issues there.
    – justinpdove
    Commented Jan 14, 2019 at 19:25
  • I think the WHERE City != NULL in the sub query is definitely a good idea to ensure only those with matching values are returned. From what I understood of the OP, The city is based off FamilyId. If this is not the case, then you could insert some CASE conditionals to handle if empty then insert b.City else insert a.City. But that then runs into some further business rules surrounding cases where FamilyId has more than 1 City, and which one to use, etc.
    – Gortonington
    Commented Jan 14, 2019 at 20:06
  • 1
    It's not entirely consistent in terms of what is empty or not empty, but this example should allow me to fit for all use cases. Just what I needed, thanks!
    – AnonWonderer
    Commented Jan 15, 2019 at 19:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .