Updating Row with Subquery Returning Multiple Rows

Question

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

The query:

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

I don't know about Oracle, but I know in SQL-Server you can't pass table names as parameters unless you're willing to dynamically build a SQL string and EXEC() it.

Hopefully an Oracle user can confirm if Oracle can do something like that.

Answer 2

You should use the alias for @Table1 in the UPDATE clause - that might even be your problem. Like this:

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

This will be much more stable, as it will realise that you're not considering it a separate table in the FROM clause.

In fact, it's really good practice to only use aliases in the UPDATE clause when you have a FROM clause as well.

Rob

Answer 3

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);

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