使用UNION更新查询-Java 学习之路

此查询适用于Oracle9i企业版9.2.0.4.0版：

UPDATE TABLE1 t1 SET t1.FIELD4=(
    SELECT SUM(t4.FIELD4)
    FROM
    (
        SELECT t2.FIELD1, t2.FIELD2, t2.FIELD4
        FROM TABLE1 t2
        UNION
        SELECT t3.FIELD1, t3.FIELD2, t3.FIELD4
        FROM TABLE2 t3
    ) t4
    WHERE t4.FIELD1=t1.FIELD1 AND t4.FIELD2=t1.FIELD2
)

但我想在SELECT子查询中移动WHERE子句，如下所示：

UPDATE TABLE1 t1 SET t1.FIELD4=(
    SELECT SUM(FIELD4)
    FROM
    (
        SELECT t2.FIELD1, t2.FIELD2, t2.FIELD4
        FROM TABLE1 t2
        WHERE t2.FIELD1=t1.FIELD1 AND t2.FIELD2=t1.FIELD2
        UNION
        SELECT t3.FIELD1, t3.FIELD2, t3.FIELD4
        FROM TABLE2 t3
        WHERE t3.FIELD1=t1.FIELD1 AND t3.FIELD2=t1.FIELD2
    )
)

但我有这个错误：

ORA-00904：“T1” . “FIELD2”：标识符无效

为什么我不能在子查询中使用t1字段？

谢谢你的帮助！

1 回答

2
Oracle返回错误的原因是您无法从相关子查询内部的多个级别引用外部查询中的列 .

（在StackOverflow的其他问题中也回答了同样的问题 . ）

UPDATE

我之前（下面）说明的方法只有在我们可以得到t1是密钥保留的内联视图时才有效 . （完全是我的坏 . 这种方法在MySQL中有效 . 我已经和MySQL合作了很长时间，以至于我期望Oracle会接受相同的语法 . ）

未指定 t1 中的 (fi,fo) 元组是唯一键 . 因此，相关子查询是要走的路 .

如果不需要保留NULL值，那么可以用零替换NULL值...
```
UPDATE TABLE1 t1 
   SET t1.fum = NVL( SELECT SUM(t2.fum)
                       FROM TABLE1 t2
                      WHERE t2.fi = t1.fi
                        AND t2.fo = t1.fo
                   ,0)
              + NVL( SELECT SUM(t3.fum)
                       FROM TABLE2 t3
                      WHERE t3.fi = t1.fi 
                        AND t3.fo = t1.fo
                   ,0)
```
NOTE: 这将返回零代替NULL;它不保留NULL值 . （如果t1.fum被定义为NOT NULL，那么在更一般的情况下这将不会出现问题，其中 t1.fum 在所有行中包含NULL，而TABLE2为空...这将用零替换NULL .

另外，如果t1.fum为NOT NULL，并且元组 (fi,fo) 是TABLE1中的唯一键，那么我们可以避免使用SELECT来获取存储在t1.fum中的值的SUM，我们已经有那个，所以我们可以......
```
... SET t1.fum = t1.fum + NVL(( correlated subquery ),0)
```
但是在更一般的情况下，我们想在t1.fum中保留NULL，我还没有一个好方法在相关子查询中引用t1而不重复子查询（ugghh）...我不知道像SUM聚合一样保留NULL的内置运算符或函数，这样我们可以在添加两个SUM子查询的数值结果时预先设置NULL .

ORIGINAL ANSWER:

（此要求的用例似乎有点奇怪;请注意UNION将消除重复的行，而UNION ALL则不会 . ）

原始答案指定了在Oracle中不起作用的多表语法 . 此方法适用于SELECT语句，但不适用于UPDATE语句 .

就个人而言，我不会使用相关子查询来执行这样的操作;我更喜欢使用JOIN操作来获得相同的结果 . 例如：
更新表1 t1
JOIN（SELECT SUM（DISTINCT r.fum）AS sum_fum，r.fi，r.fo
FROM（SELECT t2.fum
，t2.fi
，t2.fo
FROM TABLE1 t2
联盟
SELECT t3.fum
，t3.fi
，t3.fo
FROM TABLE2 t3
）r
GROUP BY r.fi，r.fo
）s
ON s.fi = t1.fi
AND s.fo = t1.fo
SET t1.fum = s.sum_fum
（如果目的是仅更新t1中的行的子集，而不是t1中的所有行，我将省略LEFT关键字以使其成为内部联接 . ）但是在我运行这样的更新之前，我将首先验证使用SELECT语句，例如：SELECT t1.fi
，t1.fo
，t1.fum AS old_fum
，s.sum_fum AS new_fum
FROM TABLE1 t1
剩下
JOIN（SELECT SUM（DISTINCT r.fum）AS sum_fum，r.fi，r.fo
FROM（SELECT t2.fum
，t2.fi
，t2.fo
FROM TABLE1 t2
联盟
SELECT t3.fum
，t3.fi
，t3.fo
FROM TABLE2 t3
）r
GROUP BY r.fi，r.fo
）s
ON s.fi = t1.fi
AND s.fo = t1.fo
一旦我确认SELECT按照我想要的方式工作，并且new_fum列中返回的值是我想要分配给t1中的fum列的值（以替换现有的old_fum值），只有这样我才会将其转换为UPDATE语句 . 为了使它成为UPDATE语句，我只需用“UPDATE”关键字替换“SELECT <list>”，只需在语句末尾添加“SET t1.fum = s.sum_fum”赋值（或之前）如果查询具有WHERE子句，则为WHERE子句 . 注意：我做了以下替换以提高可读性：FIELD1 => fi
FIELD2 => fo
FIELD4 => fum
原始查询中的内联视图使用UNION运算符而不是UNION ALL有点奇怪 . 因为UNION运算符会在将值加到一起之前删除FIELD4的任何重复值...以获得等效结果，我在SUM聚合中包含了DISTINCT关键字 . （显然，如果原始查询有一个UNION ALL运算符，那么我们将省略SUM聚合中的DISTINCT关键字 . ）我没有看到原始查询在Oracle 9中是如何“工作”的;它引用t4.FIELD1，但内联视图t4不返回名为FIELD1的列 .
回复于 2024-04-25T11:36:59+08:00

使用UNION更新查询

1 回答

相关问题