Update row with subquery returning multiple rows

Question

I get an error when updating a table with a subquery that should not have more than one value, but does.

Request:

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

Answer 1

In Oracle, you can enclose queries that return only one row (scalar subqueries) in parentheses 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);

If a subquery returns more than one row, you probably want to use SUM () in the subquery. Edit: if you are not joining tables in a 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)

Answer 2

Oracle, , SQL-Server , SQL EXEC().

, Oracle , Oracle - .

Answer 3

@Table1 UPDATE - . :

UPDATE t1 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

, , FROM.

, UPDATE, FROM.

Rob