UPDATE talkgroup SET (radios, sites) = (COALESCE(sub3.radios_new,0), COALESCE(sub3.sites_new,0)) FROM 
(SELECT talkgroup.ref as talkgroup_ref, talkgroup.radios, talkgroup.sites, sub2.ref, sub2.radios_new, sub2.sites_new FROM talkgroup 
LEFT JOIN (SELECT sub.ref, COUNT(*) AS radios_new, COUNT(DISTINCT site_ref) AS sites_new 
FROM (SELECT * 
   FROM device
   LEFT JOIN talkgroup ON talkgroup.ref = device.talkgroup_ref
   WHERE device.type = 'RADIO'
   ) sub
   WHERE sub.ref IS NOT null
   GROUP BY sub.ref
   ) sub2 ON talkgroup.ref = sub2.ref
) sub3
  WHERE talkgroup.ref = sub3.talkgroup_ref