locked
Updating parent record owner to top 1 child record owner? RRS feed

  • Question

  • I am trying to understand how to update a parent record's owner field to the value of a child record's owner field, but while there is only ever one parent record, there are multiple children, and I want to update the parent owner value to the owner of the first child record ever created for said parent.

    So for example, if the first child record created was owned by Joe Smith and the second child record created was owned by Betty Boop, I want to update the parent record to being owned by Joe Smith since he was the owner of the original child record.

    Key fields are Parent.ID, Child.ID, Parent.Owner, Child.Owner, Child.DateCreated. Hope that makes sense! It seems like using top 1 would be needed but I'm not entirely sure how to use it correctly with an update statement. I am generally pulling data not updating it, so I'm a bit out of my element.

    Thanks,

    Shaunna

     

    Sunday, February 8, 2015 11:45 PM

Answers

  • ;With cte As
    Select ParentID, Owner, DateCreated,
      Row_Number() Over(Partition By ParentID Order By DateCreated) As rn)
    Update p
    Set Owner = c.Owner
    From Parent p
    Inner Join Child c On p.ID = c.ParentID And rn = 1;

    As with any update, carefully test and be sure you have a good backup before running against a production database.

    Tom

    Monday, February 9, 2015 4:27 AM

All replies

  • I should probably add that the Child table is related to the parent table via a field called ParentID.
    Monday, February 9, 2015 2:27 AM
  • ;With cte As
    Select ParentID, Owner, DateCreated,
      Row_Number() Over(Partition By ParentID Order By DateCreated) As rn)
    Update p
    Set Owner = c.Owner
    From Parent p
    Inner Join Child c On p.ID = c.ParentID And rn = 1;

    As with any update, carefully test and be sure you have a good backup before running against a production database.

    Tom

    Monday, February 9, 2015 4:27 AM
  • UPDATE t2
    SET Owner = t1.Owner
    FROM Parent t2
    INNER JOIN Child t1
    ON t2.ID = t1.ParentID
    WHERE NOT EXISTS 
    (
    SELECT 1
    FROM Child
    WHERE ParentID = t1.ParentID
    AND DateCreated < t1.DateCreated
    )


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, February 9, 2015 5:34 AM
  • Thank you! I used the CTE version (with some tweaks).

    Shaunna

    Monday, February 9, 2015 10:26 PM