Oracle：根据其他表中的值更新表a中的列

Question

我正在尝试为我正在编写的应用程序支持多个数据库。该应用程序主要使用 Hibernate，但是当 DML 语句可以在一小部分时间内处理它们时，迭代数百万行并单独处理它们的效率非常低。因此，对于某些操作，我需要制定SQL。我更喜欢 MySQL，但到目前为止我的应用程序可以与 SQL Server 和 MySQL 配合使用。有一个操作难倒了我，我似乎无法弄清楚如何为 Oracle 构建一些更新查询。我知道就 Oracle 而言，这可能是一个新手问题，但当我一直在寻找时，我一定错过了明显的答案......

以下是我为 MySQL 编写此类查询的方式：

update table1 t1, table2 t2 set t1.colA = t2.colA where t1.colB = t2.colB and t1.colC = t2.colC

MySQL 有一个很好的构造，您可以在“set”语句之前使用别名指定所有表，这大大简化了声明的其余部分。在 SQL Server 中，我使用 update ... join 来做同样的事情。

在Oracle中，我尝试使用'update table1 set colA = (select ....) where contains (select ....) 语法，但这不起作用 - 它返回一个'子查询返回多于一行' 错误。我还尝试使用 merge ... using ... on 语法，但出现“无法从源表中获取稳定的行集”的错误。

为了进一步解释我想要实现的目标，我需要执行许多查询，其中一些使用 2 个表，一些使用 3 个表。最复杂的需要执行此操作：

使用 tableC.colC 中的值更新 tableA.colB，其中所有匹配行的 tableA.colA = tableB.colA 和 tableB.colB = tableC.colB。从数据角度来看，这看起来像这样（之前和之后）：

Before:

Table A
-------
colA     colB
1        NULL
2        NULL
3        NULL
4        NULL

Table B
-------
colA     colB
1        A
2        A
3        B
4        B

Table C
-------
colB     colC
A        15
B        20

After:

Table A
-------
colA     colB
1        15
2        15
3        20
4        20

我希望这足够清楚。谁能解释一下如何为 Oracle 编写这种 DML 查询？对于奖励积分，PostgreSQL 也会一样吗？ :)

Answer 1

您可以使用 distinct 忽略同一值的多个副本：

update  TableA
set     ColB = 
        (
        select  distinct ColC
        from    TableC C
        join    TableB B
        on      C.ColB = B.ColB
        where   B.ColA = TableA.ColA
        )

如果找到多个合适的值，这仍然会产生错误。

Answer 2

之前

    SQL> select * from A
      2  /

          COLA       COLC
    ---------- ----------
             1
             2
             3
             4

    SQL> select * from B
      2  /

          COLA C
    ---------- -
             1 A
             2 A
             3 B
             4 B

    SQL> select * from C
      2  /

    C       COLC
    - ----------
    A         15
    B         20

    SQL>

查询

    SQL> update A
      2  set colc = ( select c.colc
      3               from c
      4                      join b on ( c.colB = b.colB )
      5               where
      6                  b.colA = A.colA )
      7  where exists
      8      ( select null
      9               from c
     10                      join b on ( c.colB = b.colB )
     11               where
     12                  b.colA = A.colA )
     13  /

    4 rows updated.

    SQL>

之后

    SQL> select * from A
      2  /

          COLA       COLC
    ---------- ----------
             1         15
             2         15
             3         20
             4         20

    SQL>

显然，您已经简化了测试用例中的某些内容，因此它不能代表您的实际情况。关键是，我的查询有效，因为子查询为 A.colA 的每个值返回一行。您需要重新访问您的数据并建立必要的标准。这是一个数据/业务逻辑问题，而不是语法问题。

Answer 3

尝试以下操作：

UPDATE table_a a
  SET col_b = (SELECT col_c
                 FROM table_b b,
                      table_c c
                 WHERE b.col_a = a.col_a AND
                       c.col_b = b.col_b);

我这台机器上没有安装 Postgresql，所以无法对此发表评论。

分享并享受。