How to update table using select statement results in sql server

Question

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

Type     Total#
A           4
B           8
C           1

I want to update the above table based the result of a select statement. 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 is going to update all rows but i only want to update where Type from select statement is equal the Type from my MainTable. thanks

Answer 1

Give this a 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: when asking question like this, please add the structure of the table. It helps a lot.