使用返回多个行的子查询更新行 [英] Updating Row with Subquery Returning Multiple Rows

问题描述

在使用子查询更新表时遇到错误，该子查询不应有多个值，但确实有多个.

I'm getting an error while updating the table with a subquery which should not have more than one value, but does.

查询:

UPDATE @Table1 SET D = t2.D + t3.D 
FROM @Table1 t1 
INNER JOIN @Table2 t2 ON 
    t1.P = t2.P 
INNER JOIN @Table3 t3 ON 
    t1.A = t3.A

推荐答案

在Oracle上，您可以将仅返回一行(标量子查询)的查询括在括号中，并像使用变量/列一样使用它们:

On Oracle you can enclose queries that return only one row (scalar subqueries) in parenthesis and use them as you would use variables/columns:

UPDATE Table1 t1
SET D = (SELECT t2.D + t3.D 
         FROM Table2 t2
             ,Table3 t3
         WHERE t1.P = t2.P 
           AND t1.A = t3.A);

如果子查询返回多个行，则可能要在子查询中使用SUM(). 如果不在子查询中联接表，则可能应该使用两个子查询.

If the subquery returns more than one row you probably want to use SUM() in subquery. If you do not join tables in subquery, you should probably use two subqueries instead.

UPDATE Table1 t1
SET D = (SELECT sum(t2.D) 
         FROM Table2 t2
         WHERE t1.P = t2.P)
        +
        (SELECT sum(t3.D)
         FROM Table3 t3
         WHEREt1.A = t3.A)

这篇关于使用返回多个行的子查询更新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！