How to update table using select statement results in sql server

sql, sql-server-2008, t-sql

Solution

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.

Problem

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

Original source