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

 

I should probably add that the Child table is related to the parent table via a field called ParentID.

;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

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
)