左连接的Oracle更新操作

Question

我想简单地使用传递的参数更新一组行而不是来自另一个表，但为了识别这些行，我需要执行左连接。以下是选择查询：

SELECT *
FROM SAMPLE_STATUS pss
  LEFT JOIN QC q ON q.SAMPLE_ID = pss.SAMPLE_ID
WHERE q.CONTRACT_CLN_ID = 28 AND q.LOT = 1

我尝试了几个例子，这是最后一个只有一列的示例。

UPDATE 
  (SELECT pss.APP_WIN_START_DT, TO_CHAR(sysdate, 'YYYYMMDD') AS NEW_AWSD
  FROM SAMPLE_STATUS pss
    LEFT JOIN QC q ON q.SAMPLE_ID = pss.SAMPLE_ID
  WHERE q.CONTRACT_CLN_ID = 28 AND q.LOT = 1) pq
  SET APP_WIN_START_DT = NEW_AWSD

从QC表中提取了多条记录，通过样品ID与Sample_Status表连接。我收到的错误信息是：

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

提前感谢。

- indianapolymath

---

我猜测SAMPLE_ID列在这些表中都没有唯一或主键。 - Barbaros Özhan

Answer 1

我认为您实际上想要使用内连接逻辑，但无论如何，您都可以使用exists编写此更新操作：

UPDATE SAMPLE_STATUS pss
SET APP_WIN_START_DT = TO_CHAR(sysdate, 'YYYYMMDD')
WHERE EXISTS (SELECT 1 FROM QC q
              WHERE q.SAMPLE_ID = pss.SAMPLE_ID AND
                    q.CONTRACT_CLN_ID = 28 AND q.LOT = 1);

我怀疑你不想在这里使用左连接的原因是，你正在更新连接左侧表的字段。但由于你在右侧表的where子句中有限制条件，它会像内连接一样运作。

- Tim Biegeleisen