How to update a table using select query results in sql server

Question

I am trying to update a table where the field value is equal to the result of the select statement. I have a table like this:

Type Total# A 4 B 8 C 1 

I want to update the above table based on the select result. Here is my code:

 update MainTable set [Total#] = (SELECT count(distinct r.[ID])as Type FROM dbo.TableA r left join dbo.TableB a on r.Post_ID = a.Post_ID where a.Status is null) 

If I run the code as is, it will update all the lines, but I only want to update where Type from select statement is equal to the type from my MainTable. thanks

Answer 1

Try

 UPDATE x SET x.[Total#] = y.totalCount FROM MainTable x INNER JOIN ( SELECT [Type], COUNT(DISTINCT r.[ID]) totalCount FROM dbo.TableA r LEFT JOIN dbo.TableB a ON r.Post_ID = a.Post_ID WHERE a.STATUS IS NULL GROUP BY [Type] ) y ON x.[Type] = y.[Type] 

PS: asking this question, add the table structure. It helps a lot.

Answer 2

Give an alias for MainTable , and you can use it in a subquery:

 update MainTable mt set [Total#] = (SELECT count(distinct r.[ID]) as Type FROM dbo.TableA r left join dbo.TableB a on r.Post_ID = a.Post_ID where a.Status is null and a.AType = mt.AType ) where mt.AType = @Value