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

what are the table structures for tableA and tableB?

John Woo
– John Woo

2012-11-21 02:02:12 +00:00
Commented Nov 21, 2012 at 2:02 — John Woo
– John Woo, Commented Nov 21, 2012 at 2:02

John Woo · Accepted Answer · 2012-11-21 02:13:35Z

9

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.

answered Nov 21, 2012 at 2:13

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jachguate · Accepted Answer · 2012-11-21 01:58:26Z

0

Give an alias to your MainTable and you can use it in the 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

answered Nov 21, 2012 at 1:58

jachguate

17.2k3 gold badges62 silver badges101 bronze badges

2 Comments

moe Over a year ago

thanks but i am getting this error: Must declare the scalar variable "@Value".

jachguate Over a year ago

@moe The shown query is an example, if you don't need the where, remove it, or adjust the @value to a real value you want to filter with.

Collectives™ on Stack Overflow

How to update table using select statement results in sql server

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related