CODEWITHSUNDEEP
sqlpostgresql
user3284469
user3284469

Reputation:

Can we reduce redundancy between two subqueries in an update statement?

From Database System Concepts

Suppose we have a relation funds_received(dept_name, amount) that stores funds_received (say, by electronic funds transfer) for each of a set of departments. Suppose now that we want to add the amounts to the balances of the corresponding department budgets. In order to use the SQL update statement to carry out this task, we have to perform a look up on the funds received relation for each tuple in the department relation. We can use subqueries in the update clause to carry out this task, as follows: We assume for simplicity that the relation funds received contains at most one tuple for each department.

update department set budget = budget +
(select amount
from funds_received
where funds_received.dept_name = department.dept_name)
where exists(
select *
from funds_received
where funds_received.dept_name = department.dept_name);

Note that the condition in the where clause of the update ensures that only accounts with corresponding tuples in funds received are updated, while the sub-query within the set clause computes the amount to be added to each such department.

I was wondering why we need the where clause to first check if a department has any fund received?

The two subqueries are basically the same, and seem redundant.

Can't the following without the where clause work the same?

 update department set budget = budget +
 (select amount
 from funds_received
 where funds_received.dept_name = department.dept_name)

If a department doesn't have any received fund, then amount will be null, and budge + ... will not work?

I am interested in solutions in either SQL standard or PostgreSQL.

Thanks.

Upvotes: 0

Views: 43

Answers (2)

sticky bit
sticky bit

Reputation: 37472

You might leave the outer WHERE clause if you handle NULLs e.g. with coalesce().

UPDATE department
       SET budget = budget
                    +
                    coalesce((SELECT amount
                                     FROM funds_received
                                     WHERE funds_received.dept_name = department.dept_name), 0);

That makes sure, if an department didn't receive any funds, there is no NULL returned which might make the addition yield NULL also (it might depend on the DBMS, what happens in such a case). The coalesce() will turn NULL to 0, the neutral element of addition, so leaving the budget as it is.

(Assuming, that funds_received.amount cannot ever be NULL. If it was NULL the coalesce() cannot "know", if the NULL is there because no record was found or because the amount actually was NULL. In the case the amount is actually NULL, the original query will add NULL to the budget, my query will add a 0. So the queries weren't equivalent in this case. But I think it's very likely the author of the book had such an implicit NOT NULL constraint on funds_received.amount in mind.)

But on the other hand, the WHERE clause might reduce the rows, that have to be updated (even if the update effectively doesn't change the value, the rows need to be read and written back) and therefore benefit performance.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You need the where clause in case nothing matches. If so, then the set condition (as written) will return NULL -- probably a bad thing.

But Postgres has a better solution, using from:

update department d
    set budget = d.budget + fr.amount
from funds_received fr
where fr.dept_name = d.dept_name;

Upvotes: 2

Related Questions