Sql query result returns asterisk "*" as column value

Question

I am trying to update a temporary table with multiple values ​​from another table without using a join. However, the query does not give any error, but rather returns an asterisk as the column value. I have googled and asked some people around the office, but no one seems to have met this before or can offer an explanation of why this could happen.

update ##tempCLUnique set Total = ( select COUNT(distinct u.unique_subs) from tbl_Cluster_Cumm_Unique_Subs u where u.cluster = ##tempCLUnique.cluster ) 

Seems simple enough

Screen Results Screen http://i.stack.imgur.com/qE0ER.png

Answer 1

Use this

 update ##tempCLUnique set Total = U.unique_subs FROM ##tempCLUnique INNER JOIN ( select COUNT(distinct unique_subs)unique_subs from tbl_Cluster_Cumm_Unique_Subs )U ON u.cluster = ##tempCLUnique.cluster 

Change the connection according to your use.

Ashutosh