Thanks for posting your question in the Microsoft Q&A forum.
You can achieve this with a JOIN. It updates the TroveTotal column in t1 with the counts obtained from the subquery
CREATE PROCEDURE UpdateT1WithCounts
AS
BEGIN
UPDATE t1
SET TroveTotal = ISNULL(subquery.Count, 0)
FROM t1
LEFT JOIN (
SELECT t1.id, COUNT(DISTINCT t2.id) AS Count
FROM t1
LEFT JOIN t2 ON t2.EMR LIKE '%' + t1.Name + '%'
WHERE t1.IsActive = 1
GROUP BY t1.id
) AS subquery ON t1.id = subquery.id
END
Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful