sql - 我们可以减少更新语句中两个子查询之间的冗余吗？

Question

来自数据库系统概念

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.

我想知道为什么我们需要 where 子句来首先检查一个部门是否收到了任何资金？

这两个子查询基本相同，显得多余。

下面没有 where 子句的效果不一样吗？

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

如果一个部门没有任何收到的资金，那么amount将为空，budge + ...将不起作用？

我对 SQL 标准或 PostgreSQL 中的解决方案感兴趣。

谢谢。

Answer 1

您需要 where 子句以防万一没有匹配项。如果是这样，那么 set 条件(如所写)将返回 NULL —— 可能是一件坏事。

但是 Postgres 有一个更好的解决方案，使用 from:

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