SQL 使用带有 where 子句的“选择查询”更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3779786/
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
Update Table with a "Select query" with a where clause
提问by Stewie
I want to achieve the following:
我想实现以下目标：
Current State of table (my_table)
表的当前状态 (my_table)
id totalX totalY totalZ
--------- -------------- -------------- --------------
9 34 334 0
10 6 56 0
11 21 251 0
12 3 93 0
Query result of (my_table2)
(my_table2) 的查询结果
select id,count(*) as total FROM my_table2 WHERE column_2 = 1 GROUP BY id
id total
--------- --------------
9 500
10 600
11 700
12 800
Expected State of table (my_table)
表的预期状态 (my_table)
id totalX totalY totalZ
--------- -------------- -------------- --------------
9 34 334 500
10 6 56 600
11 21 251 700
12 3 93 800
Can this be done in ONE update query ? I am looking for Sybase ASE 12.5 on a RHEL 5.0
这可以在一次更新查询中完成吗？我正在 RHEL 5.0 上寻找 Sybase ASE 12.5
EDIT:I coudn't find the solution for Sybase, but the current answer to the question works on MS SQL Server..
编辑：我找不到 Sybase 的解决方案，但该问题的当前答案适用于 MS SQL Server ..
回答by Conrad Frix
update
my_table
set
my_table.totalZ = t.total
FROM
my_table mt
INNER JOIN
(select id,count(*) as total
FROM my_table2
WHERE column_2 = 1 GROUP BY id) t
on mt.id = t.id
UPDATEIn MS SQL Server this is what you would do. The OP noted this doesn't work in Sybase.
更新在 MS SQL Server 中，这就是您要做的。OP 指出这在 Sybase 中不起作用。
回答by jcrummack
something like this should do the trick
这样的事情应该可以解决问题
update my_table
set my_table.totalZ = (select count(*) from my_table2 where my_table.id = my_table2.id and my_table2.column_2 = 1);
回答by NotMe
It looks like Sybase supports joins with updates:
看起来 Sybase 支持带有更新的连接：
回答by Olivier Citeau
The best and more reliable way is to use MERGE.
最好和更可靠的方法是使用 MERGE。
MERGE INTO my_table
USING (select id,count(*) as total FROM my_table2 WHERE column_2 = 1 GROUP BY id) t2
ON (my_table.id=t2.id)
WHEN MATCHED THEN
UPDATE SET totalZ = t2.total
参考：http: //infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36272.1572/html/commands/commands89.htm
