本文介绍了使用“选择查询"更新表.带有where子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想实现以下目标:
表的当前状态(my_table)
id totalX totalY totalZ
--------- -------------- -------------- --------------
9 34 334 0
10 6 56 0
11 21 251 0
12 3 93 0
(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
表的预期状态(my_table)
id totalX totalY totalZ
--------- -------------- -------------- --------------
9 34 334 500
10 6 56 600
11 21 251 700
12 3 93 800
可以在一个更新查询中完成吗?我正在RHEL 5.0上寻找Sybase ASE 12.5
Can this be done in ONE update query ? I am looking for Sybase ASE 12.5 on a RHEL 5.0
编辑:我找不到Sybase的解决方案,但是该问题的当前答案适用于MS SQL Server.
I coudn't find the solution for Sybase, but the current answer to the question works on MS SQL Server..
推荐答案
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
更新,这就是您要在MS SQL Server中执行的操作. OP指出这在Sybase中不起作用.
UPDATE In MS SQL Server this is what you would do. The OP noted this doesn't work in Sybase.
这篇关于使用“选择查询"更新表.带有where子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!