在Oracle Update语句中使用子查询代替表名

Question

我需要编写一个更新语句，使用多个表来确定要更新哪些行。因为在Oracle中，不允许使用多个表进行更新。以下查询将返回“ORA-00971: Missing SET keyword”错误。

UPDATE
  TABLE1 a,
  TABLE2 b
SET
  a.COL1 = 'VALUE'
WHERE
  a.FK = b.PK
  AND b.COL2 IN ('SET OF VALUES')

在Oracle上查找UPDATE语句语法时，我发现以下链接显示可以在表名的位置使用子查询。但是当我尝试像这样编写查询时，出现了“ORA-01779: Cannot modify a column which maps to a non key-preserved table”错误提示。

UPDATE
  (
    SELECT
      a.COL1
    FROM
      TABLE1 a,
      TABLE2 b
    WHERE
      a.FK = b.PK
      AND b.COL2 IN ('SET OF VALUES')
  ) update_tbl
SET
  update_tbl.COL1 = 'VALUE'

我已经使用一个EXISTS语句将查询（如下所示）重写，并且它运行良好，但仍然想知道这是如何完成的。

UPDATE
  TABLE1 update_tbl
SET
  update_tbl.COL1 = 'VALUE'
WHERE
  EXISTS (
    SELECT
      1
    FROM
      TABLE1 a
      TABLE2 b
    WHERE
      a.FK = b.PK
      AND b.COL2 IN ('SET OF VALUES')
      AND update_tbl.PK = a.PK
  )

谢谢！ -Nate

- NateSchneider

Answer 1

另一个选择：

UPDATE TABLE1 a
SET a.COL1 = 'VALUE'
WHERE a.FK IN
( SELECT b.PK FROM TABLE2 b
  WHERE b.COL2 IN ('SET OF VALUES')
)

如果视图包括TABLE1的声明的主键，那么您的第二个示例将起作用：

UPDATE
  (
    SELECT
      a.COL1, a.PKCOL
    FROM
      TABLE1 a,
      TABLE2 b
    WHERE
      a.FK = b.PK
      AND b.COL2 IN ('SET OF VALUES')
  ) update_tbl
SET
  update_tbl.COL1 = 'VALUE'

...并且(b) TABLE1.FK是对TABLE2明确声明的外键

(所谓声明，是指有一个约束条件存在且已启用).

- Tony Andrews

Answer 2

我发现将SELECT语句快速、一致地转换为UPDATE语句的好方法是基于ROWID进行更新。

UPDATE
  TABLE1
SET
  COL1 = 'VALUE'
WHERE
  ROWID in
    (
    SELECT
      a.rowid
    FROM
      TABLE1 a,
      TABLE2 b
    WHERE
      a.FK = b.PK
      AND b.COL2 IN ('SET OF VALUES')
    )

因此，您的内部查询定义了要更新的行。

- Nick Pierpoint

Answer 3

你的示例语法是正确的，但是Oracle要求子查询包括主键。这是一个相当重要的限制。

另外，你也可以使用括号在IN语句中使用2个或更多字段，例如：

UPDATE
  TABLE1 update_tbl
SET
  update_tbl.COL1 = 'VALUE'
WHERE
  (update_tbl.PK1, update_tbl.pk2) in(
                      select some_field1, some_field2
                      from some_table st
                      where st.some_fields = 'some conditions'
                      );

- kogus

Answer 4

当您执行更新操作时，显然只能告诉系统将值更新为单个新值--告诉它将“X”更新为“Y”和“Z”没有意义。因此，当您基于内联视图的结果进行更新时，Oracle会执行检查，以确保有足够的约束条件防止修改列被潜在地更新两次。

在您的情况下，我预计TABLE2.PK实际上并没有声明为主键。如果您在该列上放置一个主键或唯一约束，则可以继续进行。

有一个未记录的提示可以绕过更新连接基数检查，由Oracle内部使用，但我不建议使用它。

这种情况的一种解决方法是使用MERGE语句，它不受相同测试的限制。

- David Aldridge

Answer 5

我在这里找到了需要的东西： 有用的SQL命令

我需要使用联接结果更新一个表格
我尝试了上面的解决方案但没有成功 :(

以下是我指向的页面的摘录
使用游标，我成功地完成了任务
我相信还有其他的解决方案，但这个解决方案可行，所以...

DECLARE

 /* Output variables to hold the result of the query: */
 a T1.e%TYPE;
 b T2.f%TYPE;
 c T2.g%TYPE;

 /* Cursor declaration: */
 CURSOR T1Cursor IS
   SELECT T1.e, T2.f, T2.g
   FROM T1, T2
   WHERE T1.id = T2.id AND T1.e <> T2.f

 FOR UPDATE;

BEGIN

  OPEN T1Cursor;

  LOOP

    /* Retrieve each row of the result of the above query
    into PL/SQL variables: */
    FETCH T1Cursor INTO a, b;

    /* If there are no more rows to fetch, exit the loop: */
    EXIT WHEN T1Cursor%NOTFOUND;

    /* Delete the current tuple: */
    DELETE FROM T1 WHERE CURRENT OF T1Cursor;

    /* Insert the reverse tuple: */
    INSERT INTO T1 VALUES(b, a);

    /* Here is my stuff using the variables to update my table */
    UPDATE T2
    SET T2.f = a
    WHERE T2.id = c;

  END LOOP;

  /* Free cursor used by the query. */
  CLOSE T1Cursor;

END;
.
run;

注意：不要忘记提交 ;-)

- Franck

Answer 6

在UPDATE子句的结果集中，每一行都必须映射回您要更新的表中的一行且只能是一行，并且以Oracle可以自动跟踪的方式。由于查询实际上是一个视图，因此可以这样考虑，即Oracle需要能够将视图与目标表连接起来，以便知道要更新哪一行。

这基本上意味着您需要在该查询中包括目标表的主键。您可能也可以使用其他唯一索引字段，但我无法保证Oracle DBMS是否足够智能以允许这样做。

- Turbulent Intellect