基于外连接结果集更新sql(update sql based on outer join result set)

不知道从哪里开始。我需要根据Oracle中外部联接和子表的查询集更新单个记录。我在MS Access中起草了一个模拟模型来帮助说明这种情况，但这是一个Oracle开发。

目标：根据其子记录的结果集更新“父”表，而无需过程 。我刚刚完成了一个逻辑过程，但需要的是做纯SQL。

表结构：

我需要根据Child.Status将Parent.Result设置为Y / N. 条件：如果有任何Child.Status =空或'N'，则Parent.Result ='N'。换句话说，Parent.Result ='Y'的唯一条件是所有Child.Status ='Y'。

以下是显示预期结果的示例数据集。

我尝试了几种方法，如计算子结果='Y'，案例陈述和其他失败。我有点坚持如何创建一个子查询来获得结果。

任何帮助或指导表示赞赏！谢谢

Not sure where to start on this one. I need to update a single record based on a queried set from an outer join and child table in Oracle. I drafted up a mock model in MS Access to help illustrate the situation, but this is an Oracle development.

Goal: update 'Parent' table based on result set of it's child records without a procedure. I would have just done a logical procedure, but the demand is to do pure SQL.

Table Structure:

I need to set Parent.Result to Y/N based on Child.Status. Conditions: If any Child.Status = Null OR 'N' then Parent.Result = 'N'. In other words, the only condition for Parent.Result = 'Y' is if all Child.Status = 'Y'.

Here is a sample data set to show expected results.

I tried a few ways like counting child results = 'Y', case statements and other failures. I'm kinda stuck on how to create a sub-query to get the result.

Any help or guidance is appreciated! Thanks

最满意答案

您可以尝试以下内容。请注意，这只是一次性更新：

UPDATE parent p SET p.result = ( CASE WHEN EXISTS ( SELECT 1 FROM join j INNER JOIN child c ON j.child_fk = c.child_pk WHERE j.parent_fk = p.parent_pk AND ( c.status IS NULL OR c.status = 'N' ) ) ) THEN 'N' ELSE 'Y' END;

如果没有孩子，你没有提到父母的结果是否可以是Y 以上假定它会。

You might try something like the following. Note that this would only be a one-time update:

You don't mention whether a parent's result can be Y if there are no children. The above assumes that it would be.

更多推荐