SQL 如何在sql server中使用select语句结果更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13485210/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update table using select statement results in sql server
提问by moe
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:
我正在尝试更新一个表，其中字段的值等于 select 语句的结果。我有一张这样的表：
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:
我想根据 select 语句的结果更新上表。这是我的代码：
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
如果我按原样运行代码，它将更新所有行，但我只想更新 select 语句中的类型等于我的 MainTable 中的类型。谢谢
回答by John Woo
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.
PS：问这样的问题时，请加上表格的结构。它有很大帮助。
回答by jachguate
Give an alias to your MainTableand you can use it in the subquery:
给你一个别名MainTable，你可以在子查询中使用它：
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
