sql-server - 用表本身的数据更新表的每一行

Question

我在更新工作表时遇到问题 - 该表中的值来自表本身。这是我的桌子：

+----------+----------+-----+---------+
| EVEN_KEY | INVE_KEY | QUA | QUA_MAX |
+----------+----------+-----+---------+
|        1 |        2 |   1 | NULL    |
|        2 |        2 |   2 | NULL    |
|        3 |        2 |   3 | NULL    |
|        1 |        1 |   2 | NULL    |
|        4 |        2 |   3 | NULL    |
+----------+----------+-----+---------+

我想做的是更新列 qua_max - 为每一行的给定 inve_key 汇总列 qua。因此，上表中的结果在更新后应如下所示：

+----------+----------+-----+---------+
| EVEN_KEY | INVE_KEY | QUA | QUA_MAX |
+----------+----------+-----+---------+
|        1 |        2 |   1 | 9       |
|        2 |        2 |   2 | 9       |
|        3 |        2 |   3 | 9       |
|        1 |        1 |   2 | 1       |
|        4 |        2 |   3 | 9       |
+----------+----------+-----+---------+

这是我的问题 -此示例中的查询给了我错误，我什至无法运行它。怎么了？

错误：

询问：

UPDATE @TEMP_FINAL
    SET QUA_MAX = (SELECT SUM(QUA)
                FROM @TEMP_FINAL t2
                WHERE @TEMP_FINAL.INVE_KEY = t2.INVE_KEY 
                GROUP BY INVE_KEY
               ) 

示例表：

DECLARE @TEMP_FINAL TABLE
(
    EVEN_KEY INT,
    INVE_KEY INT,
    QUA INT,
    QUA_MAX INT
)

insert into @TEMP_FINAL (even_key, inve_key, qua)
values(1, 2, 1), 
(2,2,2),
(3,2,3),
(1,1,2),
(4,2,3)

标签： sql-servertsqlsql-server-2012

Answer 1

你可以试试这个。。

UPDATE t1
SET t1.QUA_MAX = a.sum_qua
from @temp_final t1,
             (SELECT SUM(QUA) as sum_qua,inve_key
                FROM @TEMP_FINAL t2
                GROUP BY INVE_KEY
               ) a
where t1.INVE_KEY = a.INVE_KEY